<?php

namespace Home\Controller;
use Home\Org\Util\Autoreportsqlserver;
use Home\Org\Util\Autoreportwms;
use Home\Org\Util\Autoreportttx;

use Think\Model;

require './vendor/autoload.php';

class AutoReportController{

    protected $smsApi = 'http://sms.g-town.com.cn/sms/?service=Message.Send';
    protected $smsSysCode = 'test';
//    protected $videoCardsSmsReceiver = array('18621798517');
//    protected $videoCardsSmsReceiver = array('18621798517','18521521339','18621685371');
    protected $videoCardsSmsReceiver = array(

//        '13621967571',

        '18621798517',
        '18521521339',
        '18621685371',

        '17621067999',
        '18016348347',
        '15099930526',
        '17101719100',
        '18602157200',
        '13611996976',
        '18301712873',
        '15021836542',
        '13816961848',
        '13166389002',
        '18306228367',
        '18516220330',
        '13795430705',
        '13818857053',
//        '13061923350',
//        '13585855177',

        );

    protected $checkedCards = array(

        'YOUKU-MONTH' => array('B10023380'=>'优酷-月卡-中行积分', 'B10024549'=>'优酷-月卡-中行上海分行'),
        'YOUKU-SEASON' => array('B10023381'=>'优酷-季卡-中行积分'),
        'YOUKU-YEAR' => array('B10023382'=>'优酷-年卡-中行积分'),

        'IQIYI-SEASON' => array('B10023400'=>'爱奇艺-季卡-中行积分'),
        'IQIYI-YEAR' => array('B10023401'=>'爱奇艺-年卡-中行积分'),
        'IQIYI-MONTH' => array('B10023399'=>'爱奇艺-月卡-中行积分','B10024550'=>'爱奇艺-月卡-中行上海分行'),

        'TENCENT-SEASON' => array('B10023343'=>'腾讯视频-季卡-招行','B10023640'=>'腾讯视频-季卡-中行电子券','B10023385'=>'腾讯视频-季卡-中行积分'),
        'TENCENT-YEAR' => array('B10023344'=>'腾讯视频-年卡-招行','B10023386'=>'腾讯视频-年卡-中行积分','B10023641'=>'腾讯视频-年卡-中行电子券'),
        'TENCENT-MONTH' => array('B10023342'=>'腾讯视频-月卡-招行','B10023639'=>'腾讯视频-月卡-中行电子券','B10023384'=>'腾讯视频-月卡-中行积分','B10024548'=>'腾讯视频-月卡-中行上海分行'),
        'TENCENT-WEEK' => array('B10023711'=>'腾讯视频-周卡-招行'),

    );

    protected $checkedCardsCNName = array(

        'YOUKU-MONTH' => '优酷-月卡',
        'YOUKU-SEASON' => '优酷-季卡',
        'YOUKU-YEAR' => '优酷-年卡',

        'IQIYI-SEASON' => '爱奇艺-季卡',
        'IQIYI-YEAR' => '爱奇艺-年卡',
        'IQIYI-MONTH' => '爱奇艺-月卡',

        'TENCENT-SEASON' => '腾讯视频-季卡',
        'TENCENT-YEAR' => '腾讯视频-年卡',
        'TENCENT-MONTH' => '腾讯视频-月卡',
        'TENCENT-WEEK' => '腾讯视频-周卡',

    );

    protected $platformName = array(

        'B10023380'=>'中行私行尊享积分',
        'B10024549'=>'中行上海分行电子券项目',
        'B10023381'=>'中行私行尊享积分',
        'B10023382'=>'中行私行尊享积分',
        'B10023400'=>'中行私行尊享积分',

        'B10023401'=>'中行私行尊享积分',
        'B10023399'=>'中行私行尊享积分',
        'B10024550'=>'中行电子券',
        'B10023343'=>'招行平台',
        'B10023640'=>'中行电子券',

        'B10023385'=>'中行私行尊享积分',
        'B10023344'=>'招行平台',
        'B10023386'=>'中行私行尊享积分',
        'B10023342'=>'招行平台',
        'B10023639'=>'中行电子券',

        'B10023384'=>'中行私行尊享积分',
        'B10024548'=>'中行上海分行电子券项目',
        'B10023711'=>'招行平台',

        'B10023641'=>'中行电子券'

    );

    public function __construct(){
        ini_set('memory_limit', '512M');
    }

    // 测试输出
    function index(){
        echo "text: index";
        exit;
    }

    // 测试计划任务
    function report1x(){
        ini_set('memory_limit', '512M');
        $to = array(
            'maxwell.shi@g-town.com.cn',
        );
        $cc = array(
        );
        $this->sendReport($to, "自动任务开始执行", "",'',$cc);
        //$this->report1();
    }

    // 报表: 订单分析 开始
    function report1(){
        $to = array(
            'maxwell.shi@g-town.com.cn',
//            'tank.tan@g-town.com.cn'
        );
        $cc = array(
//            'maxwell.shi@g-town.com.cn',
//            'molly.li@g-town.com.cn',
//            'mandy.yan@g-town.com.cn',
//            'jenny.huang@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = " select   t1.u_cardcode , t1.U_CardName ,t0.u_itemcode  ,t0.u_itemname ,t0.u_qty ,t0.u_pmanager ,t1.createdate ,t0.u_docdate ,
t0.u_orderid ,t0.u_ostatus ,t0.u_memo ,min(t2.querydate) b_ship_date ,t0.u_whscode ,t0.U_expcom  ,t0.U_ExpNo ,t0.u_state ,t0.u_city ,t0.u_ispick  
from [@ti_z0081] T0 left join [@ti_z0080] T1 on T0.DocEntry=T1.DocEntry left join AX_AutoExpress_httpRequest T2 on T0.U_ExpCom = T2.ExpessCom and T0.U_ExpNo=T2.ExpressNO
where T0.U_OStatus in ('9','10') and  not exists (select 1 from AX_AutoExpress_httpRequest t3 where 
 t3.querySUMMARY in ('A1','A2') and T0.U_ExpCom = t3.ExpessCom and T0.U_ExpNo=t3.ExpressNO )   and  T1.CreateDate>='2018-04-01'  and  dateadd( hour,48,t2.queryDate)<getdate() group by t1.u_cardcode , t1.U_CardName ,t0.u_itemcode  ,t0.u_itemname,t0.u_qty ,t0.u_pmanager ,t1.createdate ,t0.u_docdate ,
t0.u_orderid ,t0.u_ostatus ,t0.u_memo ,t0.u_whscode ,t0.U_expcom  ,t0.U_ExpNo ,t0.u_state ,t0.u_city ,t0.u_ispick  ";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport1A1('已发货48小时无物流信息',$data);
        $attachment[] = $excelfile1;

        $report1SQL = "  select   B.U_cardcode ,B.U_CardName , A.U_ItemCode ,A.U_ItemName ,A.U_QTY ,A.U_PMANAGER , B.createdate ,A.u_docdate ,
A.u_orderid ,A.u_ostatus ,A.u_memo ,A.u_whscode ,A.U_expcom  ,A.U_ExpNo ,A.u_state ,A.u_city ,A.u_ispick  from  [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry  
where B.CreateDate>='2018-04-01' and dateadd(hour,48,a.u_idendate)<getdate()  and a.U_OStatus <9   and a.U_OStatus !=5  ";
        $data = $this->fetchData($report1SQL);
        $excelfile2 = $this->generateExcelReport1A2('超过2天未发货',$data);
        $attachment[] = $excelfile2;

        $report1SQL = "  select   B.U_cardcode ,B.U_CardName , A.U_ItemCode ,A.U_ItemName ,A.U_QTY ,A.U_PMANAGER , B.createdate ,A.u_docdate ,
A.u_orderid ,A.u_ostatus ,A.u_memo ,A.u_whscode ,A.U_expcom  ,A.U_ExpNo ,A.u_state ,A.u_city ,A.u_ispick  from  [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry
where B.CreateDate>='2018-04-01' and dateadd(hour,120,a.u_idendate)<getdate()  and a.U_OStatus <9   and a.U_OStatus !=5 ";
        $data = $this->fetchData($report1SQL);
        $excelfile3 = $this->generateExcelReport1A3('超过5天未发货',$data);
        $attachment[] = $excelfile3;

        $report1SQL = "  select   t1.u_cardcode , t1.U_CardName ,t0.u_itemcode  ,t0.u_itemname ,t0.u_qty ,t0.u_pmanager ,t1.createdate ,t0.u_docdate ,
t0.u_orderid ,t0.u_ostatus ,t0.u_memo ,min(t2.queryDate) b_ship_date,t0.u_whscode ,t0.U_expcom  ,t0.U_ExpNo ,t0.u_state ,t0.u_city ,t0.u_ispick 
from [@ti_z0081] T0 left join [@ti_z0080] T1 on T0.DocEntry=T1.DocEntry left join AX_AutoExpress_httpRequest T2 on T0.U_ExpCom = T2.ExpessCom and T0.U_ExpNo=T2.ExpressNO
where T0.U_OStatus in ('9','10') and  not exists (select  1 from AX_AutoExpress_httpRequest t3 where
 t3.querySUMMARY in ('A1') and T0.U_ExpCom = t3.ExpessCom and T0.U_ExpNo=t3.ExpressNO)  and  T1.CreateDate>='2018-04-01'  and  dateadd( hour,168,t2.queryDate)<getdate()  group by  t1.u_cardcode , t1.U_CardName ,t0.u_itemcode ,t0.u_itemname ,t0.u_qty ,t0.u_pmanager ,t1.createdate ,t0.u_docdate ,
t0.u_orderid,t0.u_ostatus ,t0.u_memo ,t0.u_whscode ,t0.U_expcom  ,t0.U_ExpNo,t0.u_state ,t0.u_city ,t0.u_ispick ";
        $data = $this->fetchData($report1SQL);
        $excelfile4 = $this->generateExcelReport1A4('已发货7天未签收',$data);
        $attachment[] = $excelfile4;

        $report1SQL = "  SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end as q_c_1 , b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end as q_s  ,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and   dateadd(day,7,a.CreateDate)<getdate() ";
        $data = $this->fetchData($report1SQL);
        $excelfile5 = $this->generateExcelReport1A5('超过7天未解决(售后)',$data);
        $attachment[] = $excelfile5;

        $report1SQL = "  SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end as q_c_1 , b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end as q_s  ,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and dateadd(day,2,a.CreateDate)<getdate() ";
        $data = $this->fetchData($report1SQL);
        $excelfile6 = $this->generateExcelReport1A6('超过2天未解决(售后)',$data);
        $attachment[] = $excelfile6;

        $report1SQL = "SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end as q_c_1 , b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end as q_s  ,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and dateadd(day,5,a.CreateDate)<getdate() ";
        $data = $this->fetchData($report1SQL);
        $excelfile7 = $this->generateExcelReport1A7('超过5天未解决(售后)',$data);
        $attachment[] = $excelfile7;

        $report1SQL = "SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end as q_c_1 , b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end as q_s  ,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and   dateadd(day,3,a.CreateDate)<getdate()  and  not exists  (select 1 from   [@TI_Z0216] c where a.DocEntry=c.DocEntry)";
        $data = $this->fetchData($report1SQL);
        $excelfile8 = $this->generateExcelReport1A8('投诉3天未介入(售后)',$data);
        $attachment[] = $excelfile8;

        $report1SQL = "SELECT  Z.U_cardcode ,Z.U_CardName ,SUM(Z.订单数)as dingdanshu ,SUM(Z.已发货订单数) as yifahuodingdanshu, SUM(Z.已发货无物流信息) as yifahuo48xiaoshiwuwuliuxinxi,
SUM(Z.超过2天未发货) chao2tianweifahuo,SUM(Z.超过5天未发货) chao5tianweifahuo,SUM(Z.签收数) qianshoushu,SUM(Z.未签收数) weiqianshoushu,sum(Z.已发货7天未签收) yifahuo7tianweiqianshou,
sum (Z.投诉数量) tousushuliang,SUM(Z.投诉已结案数) tousuyijiean,SUM (Z.超过2天未解决) chaoguo2tianweijiejue,
SUM(Z.超过5天未解决) chaoguo5tianweijiejue,SUM(Z.超过7天未解决) chaoguo7tianweijiejue,SUM(Z.三天未介入) santianweijieru
 FROM (
 ---1、查询当月订单数---
select B.U_cardcode,B.U_CardName ,COUNT(1)订单数,0 已发货订单数,0 已发货无物流信息,0 超过2天未发货,0 超过5天未发货,0 签收数,0 未签收数,0 已发货7天未签收,0投诉数量,0 投诉已结案数,0 超过2天未解决,0 超过5天未解决, 0 超过7天未解决,0三天未介入 from  [@TI_z0081] A   JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry where   b.CreateDate>='2018-04-01'   group by B.U_CardCode,b.u_CardName
union
--2、查询当月已发货订单数--
select B.U_cardcode,B.U_CardName ,0,COUNT(1) ,0,0,0,0,0,0,0,0,0,0,0,0  from  [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry where b.CreateDate>='2018-04-01'and a.U_OStatus  in (9,10,11,12,13,14)  group by B.U_CardCode,b.u_CardName
union
---3、48小时之前生成的快递单号仍然没有物流信息---
select t1.u_cardcode,t1.U_CardName,0,0,COUNT(distinct t0.u_orderid ),0,0,0,0,0,0,0,0,0,0,0  from [@ti_z0081] T0 left join [@ti_z0080] T1 on T0.DocEntry=T1.DocEntry left join AX_AutoExpress_httpRequest T2 on T0.U_ExpCom = T2.ExpessCom and T0.U_ExpNo=T2.ExpressNO
where T0.U_OStatus in ('9','10') and  not exists (select  1 from AX_AutoExpress_httpRequest t3 where
 t3.querySUMMARY in ('A1','A2') and T0.U_ExpCom = t3.ExpessCom and T0.U_ExpNo=t3.ExpressNO)  and  T1.CreateDate>='2018-04-01'  and  dateadd( hour,48,t2.queryDate)<getdate() group by T1.U_CardCode,T1.u_CardName
union
---4、导入单号48小时未发货--
select B.U_cardcode,B.U_CardName, 0,0,0,COUNT(1),0,0,0,0,0,0,0,0,0,0  from  [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry
where B.CreateDate>='2018-04-01'   and dateadd(hour,48,a.u_idendate)<getdate()  and a.U_OStatus <9   and a.U_OStatus !=5 group by
 B.U_CardCode,b.u_CardName
union
---5、导入单号120小时未发货--
select B.U_cardcode,B.U_CardName, 0,0,0,0,COUNT(1),0,0,0,0,0,0,0,0,0 from   [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry
where B.CreateDate>='2018-04-01'  and dateadd(hour,120,a.u_idendate)<getdate()  and a.U_OStatus <9   and a.U_OStatus !=5   group by B.U_CardCode,b.u_CardName
union
----7、签收数---
select B.U_cardcode,B.U_CardName ,0,0,0,0,0,COUNT(1),0,0,0,0,0,0,0,0 from  [@TI_z0081] A  left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry where   b.CreateDate>='2018-04-01'    and a.U_OStatus =11  group by B.U_CardCode,b.u_CardName
union
---8、未签收数---

select B.U_cardcode,B.U_CardName ,0,0,0,0,0,0,COUNT(1),0,0,0,0,0,0,0  from  [@TI_z0081] A   left JOIN  [@TI_z0080] B on a.DocEntry=b.DocEntry where   b.CreateDate>='2018-04-01'  and a.U_OStatus in ('9','10')  group by B.U_CardCode,b.u_CardName
union
--9/7天小时之前生成的快递单号仍然没有签收--
select t1.u_cardcode,t1.U_CardName,0,0,0,0,0,0,0,COUNT(distinct t0.u_orderid ),0,0,0,0,0,0  from [@ti_z0081] T0 left join [@ti_z0080] T1 on T0.DocEntry=T1.DocEntry left join AX_AutoExpress_httpRequest T2 on T0.U_ExpCom = T2.ExpessCom and T0.U_ExpNo=T2.ExpressNO
where T0.U_OStatus in ('9','10') and  not exists (select  1 from AX_AutoExpress_httpRequest t3 where
 t3.querySUMMARY in ('A1') and T0.U_ExpCom = t3.ExpessCom and T0.U_ExpNo=t3.ExpressNO)  and  T1.CreateDate>='2018-04-01'   and  dateadd( hour,168,t2.queryDate)<getdate()
 group by t1.u_cardcode,t1.u_cardname
union
--10查询各个平台投诉数量---
select U_CardCode,U_CardName,0,0,0,0,0,0,0,0,count(distinct docentry),0,0,0,0,0 投诉数 from [@TI_Z0210] where CreateDate>='2018-04-01'   group by U_CardCode,U_CardName
--11查询已完成投诉数量--
union
select a.U_CardCode,a.U_CardName,0,0,0,0,0,0,0,0,0,count(distinct a.docentry),0,0,0,0 from [@TI_Z0210]a   inner  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry  where a.CreateDate>='2018-04-01' and b.U_ProSts=3 group by U_CardCode,U_CardName
--12超过2天未解决--
union
select a.U_CardCode,a.U_CardName,0,0,0,0,0,0,0,0,0,0,count(distinct a.docentry),0,0,0 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01'  and b.U_ProSts  in (1,2,4)  and   dateadd(day,2,a.CreateDate)<getdate()
 GROUP BY a.U_CardCode,a.U_CardName
--13 超过5天未解决--
union
select a.U_CardCode,a.U_CardName,0,0,0,0,0,0,0,0,0,0,0,count(distinct a.docentry),0,0 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01'  and b.U_ProSts  in (1,2,4)  and   dateadd(day,5,a.CreateDate)<getdate()
group BY a.U_cardcode,a.U_CardName
--14超过7天未解决--
union
select a.U_CardCode,a.U_CardName,0,0,0,0,0,0,0,0,0,0,0,0,count(distinct a.docentry),0 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01'  and b.U_ProSts  in (1,2,4)  and   dateadd(day,7,a.CreateDate)<getdate()
group BY a.U_cardcode,a.U_CardName
union
--15 3天未介入
select a.U_CardCode,a.U_CardName,0,0,0,0,0,0,0,0,0,0,0,0,0,count(distinct a.docentry) from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and  dateadd(day,3,a.CreateDate)<getdate()  and  not exists  (select 1 from   [@TI_Z0216] c where a.DocEntry=c.DocEntry)
group BY a.U_cardcode,a.U_CardName )Z
group BY  Z.U_cardcode,Z.U_CardName   order by  SUM(Z.订单数) desc";
        $data = $this->fetchData($report1SQL);
        $excelfile9 = $this->generateExcelReport1A9('订单分析汇总',$data);
        $attachment[] = $excelfile9;

        $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>平台代码</td><td style='white-space: nowrap;'>平台名称</td><td style='white-space: nowrap;'>订单数</td><td style='white-space: nowrap;'>已发货订单数</td><td style='white-space: nowrap;'>已发货48小时无物流信息</td><td style='white-space: nowrap;'>超过2天未发货</td><td style='white-space: nowrap;'>超过5天未发货</td><td style='white-space: nowrap;'>签收数</td><td style='white-space: nowrap;'>未签收数</td><td style='white-space: nowrap;'>已发货7天未签收</td><td style='white-space: nowrap;'>投诉数量</td><td style='white-space: nowrap;'>投诉已结案数</td><td style='white-space: nowrap;'>超过2天未解决</td><td style='white-space: nowrap;'>超过5天未解决</td><td style='white-space: nowrap;'>超过7天未解决</td><td style='white-space: nowrap;'>三天未介入</td></tr>";
        foreach ($data as $key => $item){
            $mailConnent .= "<tr align='center'><td>".$item['U_cardcode']."</td>"."<td style='white-space: nowrap;'>".$item['U_CardName']."</td>"."<td>".$item['dingdanshu']."</td>"."<td>".$item['yifahuodingdanshu']."</td>"."<td>".$item['yifahuo48xiaoshiwuwuliuxinxi']."</td>"."<td>".$item['chao2tianweifahuo']."</td>"."<td>".$item['chao5tianweifahuo']."</td>"."<td>".$item['qianshoushu']."</td>"."<td>".$item['weiqianshoushu']."</td>"."<td>".$item['yifahuo7tianweiqianshou']."</td>"."<td>".$item['tousushuliang']."</td>"."<td>".$item['tousuyijiean']."</td>"."<td>".$item['chaoguo2tianweijiejue']."</td>"."<td>".$item['chaoguo5tianweijiejue']."</td>"."<td>".$item['chaoguo7tianweijiejue']."</td>"."<td>".$item['santianweijieru']."</td></tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "订单分析", $mailConnent,$attachment,$cc);
        exit();
    }
    // Attachment: 已发货48小时无物流信息
    protected function generateExcelReport1A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '物料代码');
        $currentSheet->setCellValue('D'.$m, '物料名称');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '产品经理');
        $currentSheet->setCellValue('G'.$m, '订单导入日期');
        $currentSheet->setCellValue('H'.$m, '单据日期');
        $currentSheet->setCellValue('I'.$m, '订单号');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '备注');
        $currentSheet->setCellValue('L'.$m, '发货时间估算');
        $currentSheet->setCellValue('M'.$m, '仓库代码');
        $currentSheet->setCellValue('N'.$m, '快递公司');
        $currentSheet->setCellValue('O'.$m, '快递单号');

        $currentSheet->setCellValue('P'.$m, '省');
        $currentSheet->setCellValue('Q'.$m, '市');
        $currentSheet->setCellValue('R'.$m, '是否拣配');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['u_cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['u_itemcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['u_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['u_docdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['b_ship_date'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['u_state'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_city'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['u_ispick'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 超过2天未发货
    protected function generateExcelReport1A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '物料代码');
        $currentSheet->setCellValue('D'.$m, '物料名称');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '产品经理');
        $currentSheet->setCellValue('G'.$m, '订单导入日期');
        $currentSheet->setCellValue('H'.$m, '单据日期');
        $currentSheet->setCellValue('I'.$m, '订单号');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '备注');
        $currentSheet->setCellValue('L'.$m, '-');
        $currentSheet->setCellValue('M'.$m, '仓库代码');
        $currentSheet->setCellValue('N'.$m, '快递公司');
        $currentSheet->setCellValue('O'.$m, '快递单号');

        $currentSheet->setCellValue('P'.$m, '省');
        $currentSheet->setCellValue('Q'.$m, '市');
        $currentSheet->setCellValue('R'.$m, '是否拣配');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_QTY'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_PMANAGER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['u_docdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['u_state'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_city'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['u_ispick'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 超过5天未发货
    protected function generateExcelReport1A3($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '物料代码');
        $currentSheet->setCellValue('D'.$m, '物料名称');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '产品经理');
        $currentSheet->setCellValue('G'.$m, '订单导入日期');
        $currentSheet->setCellValue('H'.$m, '单据日期');
        $currentSheet->setCellValue('I'.$m, '订单号');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '备注');
        $currentSheet->setCellValue('L'.$m, '-');
        $currentSheet->setCellValue('M'.$m, '仓库代码');
        $currentSheet->setCellValue('N'.$m, '快递公司');
        $currentSheet->setCellValue('O'.$m, '快递单号');

        $currentSheet->setCellValue('P'.$m, '省');
        $currentSheet->setCellValue('Q'.$m, '市');
        $currentSheet->setCellValue('R'.$m, '是否拣配');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_QTY'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_PMANAGER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['u_docdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['u_state'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_city'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['u_ispick'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 已发货7天未签收
    protected function generateExcelReport1A4($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '物料代码');
        $currentSheet->setCellValue('D'.$m, '物料名称');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '产品经理');
        $currentSheet->setCellValue('G'.$m, '订单导入日期');
        $currentSheet->setCellValue('H'.$m, '单据日期');
        $currentSheet->setCellValue('I'.$m, '订单号');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '备注');
        $currentSheet->setCellValue('L'.$m, '发货时间估算');
        $currentSheet->setCellValue('M'.$m, '仓库代码');
        $currentSheet->setCellValue('N'.$m, '快递公司');
        $currentSheet->setCellValue('O'.$m, '快递单号');

        $currentSheet->setCellValue('P'.$m, '省');
        $currentSheet->setCellValue('Q'.$m, '市');
        $currentSheet->setCellValue('R'.$m, '是否拣配');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['u_cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['u_itemcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['u_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['u_docdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_memo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['b_ship_date'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['U_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['u_state'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_city'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['u_ispick'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 超过7天未解决(售后)
    protected function generateExcelReport1A5($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');
        $currentSheet->setCellValue('T'.$m, '投诉日期');

        $currentSheet->setCellValue('U'.$m, '投诉时间');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '投诉次数描述');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['q_c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['q_s'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_comhours'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['u_comments'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['u_accuname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 超过2天未解决(售后)
    protected function generateExcelReport1A6($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');
        $currentSheet->setCellValue('T'.$m, '投诉日期');

        $currentSheet->setCellValue('U'.$m, '投诉时间');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '投诉次数描述');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['q_c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['q_s'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_comhours'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['u_comments'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['u_accuname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 超过5天未解决(售后)
    protected function generateExcelReport1A7($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');
        $currentSheet->setCellValue('T'.$m, '投诉日期');

        $currentSheet->setCellValue('U'.$m, '投诉时间');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '投诉次数描述');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['q_c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['q_s'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_comhours'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['u_comments'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['u_accuname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 投诉3天未介入(售后)
    protected function generateExcelReport1A8($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['q_c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['q_s'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 汇总信息
    protected function generateExcelReport1A9($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台代码');
        $currentSheet->setCellValue('B'.$m, '平台名称');
        $currentSheet->setCellValue('C'.$m, '订单数');
        $currentSheet->setCellValue('D'.$m, '已发货订单数');
        $currentSheet->setCellValue('E'.$m, '已发货48小时无物流信息');

        $currentSheet->setCellValue('F'.$m, '超过2天未发货');
        $currentSheet->setCellValue('G'.$m, '超过5天未发货');
        $currentSheet->setCellValue('H'.$m, '签收数');
        $currentSheet->setCellValue('I'.$m, '未签收数');
        $currentSheet->setCellValue('J'.$m, '已发货7天未签收');

        $currentSheet->setCellValue('K'.$m, '投诉数量');
        $currentSheet->setCellValue('L'.$m, '投诉已结案数');
        $currentSheet->setCellValue('M'.$m, '超过2天未解决');
        $currentSheet->setCellValue('N'.$m, '超过5天未解决');
        $currentSheet->setCellValue('O'.$m, '超过7天未解决');

        $currentSheet->setCellValue('P'.$m, '三天未介入');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['dingdanshu'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['yifahuodingdanshu'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['yifahuo48xiaoshiwuwuliuxinxi'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['chao2tianweifahuo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['chao5tianweifahuo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['qianshoushu'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['weiqianshoushu'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['yifahuo7tianweiqianshou'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['tousushuliang'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['tousuyijiean'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['chaoguo2tianweijiejue'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['chaoguo5tianweijiejue'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['chaoguo7tianweijiejue'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['santianweijieru'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 订单分析 结束

    // 报表: 联谊路发货表-WMS 开始
    function report3(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select  ERP_ORDER ,total_qty ,total_lines ,DATE_TIME_STAMP  ,carrier ,user_def8     from  shipment_header  where  dateadd(DAY,2,DATE_TIME_STAMP)>=getdate()";
        $data = $this->fetchDataWMS($report1SQL);
        $excelfile1 = $this->generateExcelReport3A1('联谊路发货表',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>数据:当前时间点上溯48小时内所有下发联谊路仓库订单明细</div>";

        $this->sendReport($to, "联谊路发货表", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 联谊路发货表
    protected function generateExcelReport3A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'SAP订单号');
        $currentSheet->setCellValue('B'.$m, '数量');
        $currentSheet->setCellValue('C'.$m, '行数');
        $currentSheet->setCellValue('D'.$m, '发货时间');
        $currentSheet->setCellValue('E'.$m, '快递公司');

        $currentSheet->setCellValue('F'.$m, '快递单号');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['ERP_ORDER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['total_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['total_lines'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['DATE_TIME_STAMP'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['carrier'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['user_def8'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 联谊路发货表 结束

    // 报表: 联谊路收货数据监控每小时/次 开始
    function report6(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select   b.RECEIPT_ID ,b.ITEM ,b.ITEM_DESC ,b.QUANTITY ,b.DATE_TIME_STAMP ,a.ERP_ORDER_ID  from  [dbo].[RECEIPT_CONTAINER] b  join [dbo].[RECEIPT_HEADER] a ON a.RECEIPT_ID=b.RECEIPT_ID
WHERE  dateadd(HOUR,1,b.DATE_TIME_STAMP)>=getdate()";
        $data = $this->fetchDataWMS($report1SQL);

        if($data) {

            $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>收货ID</td><td style='white-space: nowrap;'>物料编码</td><td style='white-space: nowrap;'>物料描述</td><td style='white-space: nowrap;'>收货数量</td><td style='white-space: nowrap;'>收货时间</td><td style='white-space: nowrap;'>SAP采购单号</td></tr>";
            foreach ($data as $key => $item) {
                $mailConnent .= "<tr align='center'><td>" . $item['RECEIPT_ID'] . "</td>" . "<td style='white-space: nowrap;'>" . $item['ITEM'] . "</td>" . "<td>" . $item['ITEM_DESC'] . "</td>" . "<td>" . $item['QUANTITY'] . "</td>" . "<td>" . $item['DATE_TIME_STAMP'] . "</td>" . "<td>" . $item['ERP_ORDER_ID'] . "</td></tr>";
            }
            $mailConnent .= "</table>";

            $this->sendReport($to, "联谊路收货数据监控每小时/次", $mailConnent, '', $cc);

        }
        exit();

    }
    // 报表: 联谊路收货数据监控每小时/次 结束

    // 报表: 广发银行C20020每日订单分析报表 开始
    function report9(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select A.CODE   ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName , sum(b.onhand) the_stock  from [@ti_z0041] A  left   join OITW b on a.U_ItemCode=b.ItemCode
where    b.whscode in  ('A02','H03','HYWCK')  and A.code='C20020'  group by  A.CODE  ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName  order by sum(b.onhand) desc";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport9A1('库存明细',$data);
        $attachment[] = $excelfile1;

        $report1SQL = "select  a.U_CardCode ,a.U_CardName ,a.CreateDate ,b.U_OrderId ,b.u_qty ,b.U_Price ,
b.U_Total ,b.U_ItemCode ,b.U_ItemName ,b.u_ostatus , b.u_expcom ,b.u_expno  from [@TI_Z0080]
a  join [@TI_Z0081] b on a.DocEntry=b.DocEntry  where a.U_CardCode='C20020' and dateadd(DAY,1,a.CreateDate)>=getdate()";
        $data = $this->fetchData($report1SQL);
        $excelfile2 = $this->generateExcelReport9A2('订单明细',$data);
        $attachment[] = $excelfile2;

        $report1SQL = "SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end c_1, b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end  as the_status,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry  
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and   dateadd(day,1,a.CreateDate)<getdate()  and A.U_CardCode='C20020'";
        $data = $this->fetchData($report1SQL);
        $excelfile3 = $this->generateExcelReport9A3('售后明细',$data);
        $attachment[] = $excelfile3;

        $mailConnent = "<div>1、库存明细为当前联谊路、3号仓、虚拟卡券仓可用库存之和；
2、订单明细、售后明细为当前时间点上溯24小时内订单数据；
3、SAP订单状态说明：
1 未处理
2 订单确认
3 待处理
4 异常订单
5 未发货取消
6 异常已导出
7 已发订单确认短信
8 可拣配
9 已发货
10 已发签收短信
11 确认发货
12 已发货待取消
13 丢件/损坏
14 已退回</div>";

        $this->sendReport($to, "广发银行C20020每日订单分析报表", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 库存明细
    protected function generateExcelReport9A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台代码');
        $currentSheet->setCellValue('B'.$m, 'SAP物料代码');
        $currentSheet->setCellValue('C'.$m, 'SAP物料名称');
        $currentSheet->setCellValue('D'.$m, '客户物料代码');
        $currentSheet->setCellValue('E'.$m, '客户物料名称');

        $currentSheet->setCellValue('F'.$m, '库存');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['CODE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['u_itemcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_CItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_CItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['the_stock'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 订单明细
    protected function generateExcelReport9A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '订单创建日期');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '单价');
        $currentSheet->setCellValue('G'.$m, '总额');
        $currentSheet->setCellValue('H'.$m, '物料号');
        $currentSheet->setCellValue('I'.$m, '物料名称');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '快递公司');
        $currentSheet->setCellValue('L'.$m, '快递单号');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['CreateDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['u_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_Price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_Total'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 售后明细
    protected function generateExcelReport9A3($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');
        $currentSheet->setCellValue('T'.$m, '投诉日期');

        $currentSheet->setCellValue('U'.$m, '投诉时间');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '投诉次数描述');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['the_status'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_comhours'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['u_comments'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['u_accuname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 广发银行C20020每日订单分析报表 结束

    // 报表: 中国银行积分商城C20093库存为0商品提醒 开始
    function report2(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select A.CODE   ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName , sum(b.onhand) as ONHAND from [@ti_z0041] A  left   join OITW b on a.U_ItemCode=b.ItemCode
where    b.whscode in  ('A02','H03','HYWCK')  and A.code='C20093'  group by  A.CODE  ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName  having sum(b.onhand)=0";
        $data = $this->fetchData($report1SQL);

        $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>平台代码</td><td style='white-space: nowrap;'>SAP物料代码</td><td style='white-space: nowrap;'>SAP物料名称</td><td style='white-space: nowrap;'>客户物料代码</td><td style='white-space: nowrap;'>客户物料名称</td><td style='white-space: nowrap;'>-</td></tr>";
        foreach ($data as $key => $item){
            $mailConnent .= "<tr align='center'><td>".$item['CODE']."</td>"."<td style='white-space: nowrap;'>".$item['u_itemcode']."</td>"."<td>".$item['u_itemname']."</td>"."<td>".$item['U_CItemCode']."</td>"."<td>".$item['U_CItemName']."</td>"."<td>".$item['ONHAND']."</td></tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "中国银行积分商城C20093库存为0商品提醒", $mailConnent,$attachment,$cc);
        exit();

    }
    // 报表: 中国银行积分商城C20093库存为0商品提醒 结束

    // 报表: 过去24小时确认收货的订单明细 开始
    function report4(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select T1.U_Cardcode ,T1.U_CardName ,U_OrderId , U_Cstname , U_Phone ,U_ExpCom ,U_ExpNo ,SignReceivedDatetime ,Convert(decimal(18,2),T0.U_Total ) as U_Total from [@TI_Z0081] T0 left join [@TI_Z0080] T1 on T0.DocEntry=T1.DocEntry
left join 
(select * from AX_AutoExpress_httpRequest where querySUMMARY = 'A1' AND summaryDate>DATEADD(D,-2,GETDATE())) T2
on replace(T0.U_ExpNo,' ','') = replace(T2.ExpressNO,' ','')
Where replace(u_expcom,' ','')+'#'+replace(u_expno,' ','') in 
(select replace(relatedvalue,' ','') relatedvalue from AX_AutoExpress_SAPupdatelog where dealDate>DATEADD(hour,-25,getdate()))
and u_orderid not in (select u_orderid from [@ti_z0210] where U_OrderId is not  null union all select u_orderid from [@ti_Z0220] where U_OrderId is not  null)
group by T1.U_Cardcode, U_OrderId,T1.U_CardName,U_Cstname,U_Phone,U_ExpCom,U_ExpNo,SignReceivedDatetime,T0.U_Total
order by u_cardcode";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport4A1('过去24小时确认收货的订单明细',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>每天发送一次。原订单状态为《已发货》《已发签收短信》，在过去24小时内，查询到快递已经签收记录，并自动更新SAP订单状态到《确认收货》</div>";

        $this->sendReport($to, "过去24小时确认收货的订单明细", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 联谊路发货表
    protected function generateExcelReport4A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台代码');
        $currentSheet->setCellValue('B'.$m, '平台名称');
        $currentSheet->setCellValue('C'.$m, '订单编号');
        $currentSheet->setCellValue('D'.$m, '客户姓名');
        $currentSheet->setCellValue('E'.$m, '客户手机号');

        $currentSheet->setCellValue('F'.$m, '快递公司');
        $currentSheet->setCellValue('G'.$m, '快递单号');
        $currentSheet->setCellValue('H'.$m, '签收日期');
        $currentSheet->setCellValue('I'.$m, '总金额');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_Cardcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_Cstname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_Phone'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_ExpCom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_ExpNo'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['SignReceivedDatetime'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_Total'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 过去24小时确认收货的订单明细 结束

    // 报表: C20111招商银行订单分析 开始
    function report5(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select  a.U_CardCode ,a.U_CardName ,a.CreateDate ,b.U_OrderId ,b.u_qty ,b.U_Price ,b.U_Total ,b.U_ItemCode ,b.U_ItemName ,b.u_ostatus , b.u_expcom ,b.u_expno  from [@TI_Z0080]  a  join [@TI_Z0081] b on a.DocEntry=b.DocEntry  where a.U_CardCode='C20111' and dateadd(DAY,30,a.CreateDate)>=getdate()";
        $data = $this->fetchData($report1SQL);
        $excelfile2 = $this->generateExcelReport5A2('订单明细',$data);
        $attachment[] = $excelfile2;

        $report1SQL = "SELECT  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end as c_1, b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,b.U_AccuDetl  ,b.U_AccuQDtl ,b.u_qsummary , case b.u_prosts when '1' then '未完成'  when '2' then '处理中'  when '3' then '已完成'
 when '4' then '异常' end  as d_state,b.u_whscode ,c.u_comdate ,
c.u_comhours ,c.u_comments ,c.u_accuname
 from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry  
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts  in (1,2,4)  and   dateadd(day,30,a.CreateDate)<getdate()  and A.U_CardCode='C20111'";
        $data = $this->fetchData($report1SQL);
        $excelfile3 = $this->generateExcelReport5A3('售后明细',$data);
        $attachment[] = $excelfile3;

        $report1SQL = "select A.CODE   ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName , sum(b.onhand) ONHAND  from [@ti_z0041] A  left   join OITW b on a.U_ItemCode=b.ItemCode
where    b.whscode in  ('A02','H03','HYWCK')  and A.code='C20111'  group by  A.CODE  ,A.u_itemcode  ,a.u_itemname  ,A.U_CItemCode ,A.U_CItemName
";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport5A1('在库库存',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>平台代码</td><td style='white-space: nowrap;'>SAP物料代码</td><td style='white-space: nowrap;'>SAP物料名称</td><td style='white-space: nowrap;'>客户物料代码</td><td style='white-space: nowrap;'>客户物料名称</td><td style='white-space: nowrap;'>库存</td></tr>";
        foreach ($data as $key => $item){
            $mailConnent .= "<tr align='center'><td>".$item['CODE']."</td>"."<td style='white-space: nowrap;'>".$item['u_itemcode']."</td>"."<td>".$item['u_itemname']."</td>"."<td>".$item['U_CItemCode']."</td>"."<td>".$item['U_CItemName']."</td>"."<td>".$item['ONHAND']."</td></tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "C20111招商银行订单分析", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 在库库存
    protected function generateExcelReport5A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台代码');
        $currentSheet->setCellValue('B'.$m, 'SAP物料代码');
        $currentSheet->setCellValue('C'.$m, 'SAP物料名称');
        $currentSheet->setCellValue('D'.$m, '客户物料代码');
        $currentSheet->setCellValue('E'.$m, '客户物料名称');

        $currentSheet->setCellValue('F'.$m, '库存');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['CODE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['u_itemcode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_CItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_CItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['ONHAND'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 订单明细
    protected function generateExcelReport5A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '订单创建日期');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '数量');

        $currentSheet->setCellValue('F'.$m, '单价');
        $currentSheet->setCellValue('G'.$m, '总额');
        $currentSheet->setCellValue('H'.$m, '物料号');
        $currentSheet->setCellValue('I'.$m, '物料名称');
        $currentSheet->setCellValue('J'.$m, '订单状态');

        $currentSheet->setCellValue('K'.$m, '快递公司');
        $currentSheet->setCellValue('L'.$m, '快递单号');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['CreateDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['u_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_Price'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_Total'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_ostatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 售后明细
    protected function generateExcelReport5A3($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户代码');
        $currentSheet->setCellValue('B'.$m, '客户名称');
        $currentSheet->setCellValue('C'.$m, '售后单号');
        $currentSheet->setCellValue('D'.$m, '订单号');
        $currentSheet->setCellValue('E'.$m, '物料代码');

        $currentSheet->setCellValue('F'.$m, '颜色');
        $currentSheet->setCellValue('G'.$m, '物料名称');
        $currentSheet->setCellValue('H'.$m, '一级分类');
        $currentSheet->setCellValue('I'.$m, '二级分类');
        $currentSheet->setCellValue('J'.$m, '快递公司');

        $currentSheet->setCellValue('K'.$m, '快递单号');
        $currentSheet->setCellValue('L'.$m, '产品经理');
        $currentSheet->setCellValue('M'.$m, '客服');
        $currentSheet->setCellValue('N'.$m, '订单导入日期');
        $currentSheet->setCellValue('O'.$m, '售前问题描述');

        $currentSheet->setCellValue('P'.$m, '售后');
        $currentSheet->setCellValue('Q'.$m, '问题描述');
        $currentSheet->setCellValue('R'.$m, '处理状态');
        $currentSheet->setCellValue('S'.$m, '仓库');
        $currentSheet->setCellValue('T'.$m, '投诉日期');

        $currentSheet->setCellValue('U'.$m, '投诉时间');
        $currentSheet->setCellValue('V'.$m, '备注');
        $currentSheet->setCellValue('W'.$m, '投诉次数描述');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['U_AccuDetl'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['d_state'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_comhours'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['u_comments'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['u_accuname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: C20111招商银行订单分析 结束

    // 报表: 每日库存报表 开始
    function report11(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select A.ITEMCODE ,B.ITEMNAME ,sum (A.OnHand) ONHAND ,sum(A.onorder) ONORDER, sum(A.U_TI_Z0_IsCommited) ONCONFIRM,sum(A.U_TI_Z0_IsJp) ONRUN
from  OITW  A   join  oitm B  on  a.itemcode=b.itemcode  where a.ItemCode  like '%B%'  and   a.whscode in  ('A02','H03','HYWCK')   group  by A.ITEMCODE,B.ITEMNAME";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport11A1('每日库存报表',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>说明:存货量/已订购/已承诺/已捡配均取（HYWCK/A02/H03）仓库之和，报表每日早上10：30/下午5：00各发一次。</div>";

        $this->sendReport($to, "每日库存报表", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 每日库存报表
    protected function generateExcelReport11A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编码');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '存货量');
        $currentSheet->setCellValue('D'.$m, '已订购');
        $currentSheet->setCellValue('E'.$m, '已承诺');

        $currentSheet->setCellValue('F'.$m, '已拣配');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['ITEMCODE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['ITEMNAME'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['ONHAND'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['ONORDER'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['ONCONFIRM'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['ONRUN'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 每日库存报表 结束

    // 报表: 售后异常单-2018年4月1日至今 开始
    function report7(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "  SELECT max(c.u_comdate) u_comdate, A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,a.U_CstName ,b.U_ItemCode ,d.u_color ,b.u_itemname , case b.U_AccuQCls when '1' then '质量问题'  when '2' then '快递运输问题'  when '3' then '发票问题'
 when '4' then '商品信息问题'  when '5' then '发货问题'  when '6' then '其他' end c_1, b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate ,SUBSTRING(b.U_AccuDetl,1,100)  q_detail,SUBSTRING(b.U_AccuQDtl,1,100) U_AccuQDtl,SUBSTRING(b.u_qsummary,1,100) u_qsummary,SUBSTRING(b.u_prosts,1,100)   as d_state,b.u_whscode  from [@TI_Z0210] a   left  join [@TI_Z0212] b  on a.DocEntry=b.DocEntry   LEFT  join [@TI_Z0216] c  on a.DocEntry=c.DocEntry
 join [@ti_z0081] d  on  a.u_orderid=d.u_orderid
where a.CreateDate>='2018-04-01' and b.U_ProSts='4' 
 group by  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname ,  A.U_CardCode ,A.U_CardName ,A.DocEntry  ,A.u_orderid ,b.U_ItemCode ,d.u_color ,b.u_itemname,
b.U_AccuQCls ,b.U_CompName ,d.u_expcom ,d.u_expno ,d.u_pmanager ,
b.u_dealer ,A.createdate,SUBSTRING(b.U_AccuDetl,1,100) ,SUBSTRING(b.U_AccuQDtl,1,100),SUBSTRING(b.u_qsummary,1,100) ,SUBSTRING(b.u_prosts,1,100) ,b.u_whscode, c.u_comhours, substring(c.u_comments,1,100),a.U_CstName";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport7A1('售后异常单-2018年4月1日至今',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div></div>";

        $this->sendReport($to, "售后异常单-2018年4月1日至今", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 售后异常单-2018年4月1日至今
    protected function generateExcelReport7A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '投诉日期');
        $currentSheet->setCellValue('B'.$m, '客户代码');
        $currentSheet->setCellValue('C'.$m, '客户名称');
        $currentSheet->setCellValue('D'.$m, '售后单号');
        $currentSheet->setCellValue('E'.$m, '订单号');

        $currentSheet->setCellValue('F'.$m, '客户姓名');
        $currentSheet->setCellValue('G'.$m, '物料代码');
        $currentSheet->setCellValue('H'.$m, '颜色');
        $currentSheet->setCellValue('I'.$m, '物料名称');
        $currentSheet->setCellValue('J'.$m, '一级分类');

        $currentSheet->setCellValue('K'.$m, '二级分类');
        $currentSheet->setCellValue('L'.$m, '快递公司');
        $currentSheet->setCellValue('M'.$m, '快递单号');
        $currentSheet->setCellValue('N'.$m, '产品经理');
        $currentSheet->setCellValue('O'.$m, '客服');

        $currentSheet->setCellValue('P'.$m, '订单导入日期');
        $currentSheet->setCellValue('Q'.$m, '售前问题描述');
        $currentSheet->setCellValue('R'.$m, '售后');
        $currentSheet->setCellValue('S'.$m, '问题描述');
        $currentSheet->setCellValue('T'.$m, '处理状态');

        $currentSheet->setCellValue('U'.$m, '仓库');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['u_comdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_CardCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['u_orderid'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_CstName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['u_color'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['c_1'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['U_CompName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['u_expcom'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['u_expno'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['u_pmanager'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['u_dealer'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['q_detail'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['U_AccuQDtl'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['u_qsummary'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['d_state'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['u_whscode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 售后异常单-2018年4月1日至今 结束

    // 报表: 重点客户预警！！！ 开始
    function report8(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select a.U_CardName ,a.U_OrderId ,a.U_ItemCode ,a.U_CitemNm ,a.U_Cstname ,a.U_Phone ,a.U_address  from [@TI_Z0081] a join [@TI_Z0080] b  on a.DocEntry=b.DocEntry
where b.createdate>dateadd(HOUR,-4,getdate()) and (u_cstname+U_Address like '%赵海翡%' OR
u_cstname+U_Address like '%李宇婷%' OR
u_cstname+U_Address like '%彭艳珊%' OR
u_cstname+U_Address like '%汪和明%' OR
u_cstname+U_Address like '%杨金霖%' OR
u_cstname+U_Address like '%樊珮瑜%' OR
u_cstname+U_Address like '%廖晖%' OR
u_cstname+U_Address like '%郑海涛%' OR
u_cstname+U_Address like '%高德置地%' OR
u_cstname+U_Address like '%东风东路713号%' OR
u_cstname+U_Address like '%锦绣花园B25栋%'  OR
u_cstname+U_Address like '%翁雅玲%'  OR
u_cstname+U_Address like '%漕溪北路333号%' OR
u_cstname+U_Address like '%李欣%' OR
u_cstname+U_Address like '%钟晨%' OR
u_cstname+U_Address like '%徐娟娟%' OR
u_cstname+U_Address like '%朱亦红%' OR
u_cstname+U_Address like '%赵欢%' OR
u_cstname+U_Address like '%许丹丹%' OR
u_cstname+U_Address like '%傅思慧%'  OR
u_cstname+U_Address like '%珠江西路10号%')";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport8A1('重点客户预警',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>说明:1,客户姓名或地址包含以下关键字时触发预警，程序4小时反馈一次结果，无结果时不反馈，如需更改条件请提报IT部。 '%李宇婷%''%彭艳珊%''%汪和明%''%杨金霖%''%樊珮瑜%''%廖晖%''%郑海涛%'%翁雅玲%'%李欣%''%钟晨%''%徐娟娟%''%朱亦红%''%赵欢%''%许丹丹%''%傅思慧%''%高德置地%''%东风东路713号%''%锦绣花园B25栋%''%漕溪北路333号%'%珠江西路10号% 2，本邮件由系统自动发出，请勿直接回复。</div>";

        $this->sendReport($to, "重点客户预警", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 重点客户预警
    protected function generateExcelReport8A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '客户名称');
        $currentSheet->setCellValue('B'.$m, '订单号');
        $currentSheet->setCellValue('C'.$m, 'SAP物料编码');
        $currentSheet->setCellValue('D'.$m, '物料名称');
        $currentSheet->setCellValue('E'.$m, '客户姓名');

        $currentSheet->setCellValue('F'.$m, '手机号码');
        $currentSheet->setCellValue('G'.$m, '收货地址');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_CardName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['U_CitemNm'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['U_Cstname'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['U_Phone'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['U_address'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 重点客户预警！！！ 结束

    // 报表: wms推送失败采购单信息（IT运维组专用） 开始
    function report10(){

        $to = array(
             'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select   *  from   dbo.UPLAOD_RECEIPT  where  RecStatus=1   and CRDT>='2018-05-01'";
        $data = $this->fetchDataTTX($report1SQL);
        $excelfile1 = $this->generateExcelReport10A1('wms推送失败采购单信息',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div></div>";

        $this->sendReport($to, "wms推送失败采购单信息（IT运维组专用）", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: wms推送失败采购单信息
    protected function generateExcelReport10A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'Id');
        $currentSheet->setCellValue('B'.$m, 'COMPANY');
        $currentSheet->setCellValue('C'.$m, 'WAREHOUSE');
        $currentSheet->setCellValue('D'.$m, 'RECEIPT_ID');
        $currentSheet->setCellValue('E'.$m, 'RECEIPT_TYPE');

        $currentSheet->setCellValue('F'.$m, 'Remark');
        $currentSheet->setCellValue('G'.$m, 'OrderDate');
        $currentSheet->setCellValue('H'.$m, 'ITEM');
        $currentSheet->setCellValue('I'.$m, 'InventorySts');
        $currentSheet->setCellValue('J'.$m, 'ItemName');

        $currentSheet->setCellValue('K'.$m, 'Lot');
        $currentSheet->setCellValue('L'.$m, 'ItemCount');
        $currentSheet->setCellValue('M'.$m, 'ItemStatus');
        $currentSheet->setCellValue('N'.$m, 'CID');
        $currentSheet->setCellValue('O'.$m, 'RecStatus');

        $currentSheet->setCellValue('P'.$m, 'CRDT');
        $currentSheet->setCellValue('Q'.$m, 'MDDT');
        $currentSheet->setCellValue('R'.$m, 'SendCount');
        $currentSheet->setCellValue('S'.$m, 'USER_DEF1');
        $currentSheet->setCellValue('T'.$m, 'USER_DEF2');

        $currentSheet->setCellValue('U'.$m, 'USER_DEF3');
        $currentSheet->setCellValue('V'.$m, 'USER_DEF4');
        $currentSheet->setCellValue('W'.$m, 'interface_message');
        $currentSheet->setCellValue('X'.$m, 'Series');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['Id'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['COMPANY'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['WAREHOUSE'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['RECEIPT_ID'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['RECEIPT_TYPE'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['Remark'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['OrderDate'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['ITEM'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['InventorySts'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['ItemName'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['Lot'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['ItemCount'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('M'.$cellIndex,$v['ItemStatus'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('N'.$cellIndex,$v['CID'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('O'.$cellIndex,$v['RecStatus'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('P'.$cellIndex,$v['CRDT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('Q'.$cellIndex,$v['MDDT'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('R'.$cellIndex,$v['SendCount'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('S'.$cellIndex,$v['USER_DEF1'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('T'.$cellIndex,$v['USER_DEF2'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('U'.$cellIndex,$v['USER_DEF3'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('V'.$cellIndex,$v['USER_DEF4'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('W'.$cellIndex,$v['interface_message'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('X'.$cellIndex,$v['Series'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: wms推送失败采购单信息（IT运维组专用） 结束

    // 报表: 代发反馈异常,请及时处理 开始
    function report12(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select * from [dbo].[DropShippingError]";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport12A1('代发反馈异常',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div></div>";

        $this->sendReport($to, "代发反馈异常", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 代发反馈异常
    protected function generateExcelReport12A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'ID');
        $currentSheet->setCellValue('B'.$m, 'DocEntry');
        $currentSheet->setCellValue('C'.$m, 'ErrorMessage');
        $currentSheet->setCellValue('D'.$m, 'ErrorDate');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['ID'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['DocEntry'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['ErrorMessage'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['ErrorDate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 代发反馈异常,请及时处理 结束

    // 报表: 电子卡券发货提醒 开始
    function report13(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select a.U_ItemCode,a.u_itemname,a.U_OrderId,a.u_qty,b.createdate, U_PickNo ID  from  [@TI_Z0081] a left  join [@TI_Z0080] b  on a.docentry=b.DocEntry where a.U_ItemCode  in('B10020556','B10014409')  and a.U_OStatus=8 and a.U_PickNo!=0  and  a.u_docdate>='2018-04-01'";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport13A1('电子卡券发货提醒',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div></div>";

        $this->sendReport($to, "电子卡券发货提醒", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 电子卡券发货提醒
    protected function generateExcelReport13A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, 'U_ItemCode');
        $currentSheet->setCellValue('B'.$m, 'u_itemname');
        $currentSheet->setCellValue('C'.$m, 'U_OrderId');
        $currentSheet->setCellValue('D'.$m, 'u_qty');
        $currentSheet->setCellValue('E'.$m, 'createdate');

        $currentSheet->setCellValue('F'.$m, 'ID');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['U_ItemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['u_itemname'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['U_OrderId'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['u_qty'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['createdate'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['ID'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 电子卡券发货提醒 结束

    // 报表: 联谊路仓库收货明细上溯24小时数据 开始
    function report14(){

        $to = array(
            'molly.li@g-town.com.cn'
        );
        $cc = array(
            'maxwell.shi@g-town.com.cn'
        );

        $attachment = array();

        $report1SQL = "select   b.RECEIPT_ID ,b.ITEM ,b.ITEM_DESC ,b.QUANTITY ,b.DATE_TIME_STAMP ,a.ERP_ORDER_ID  from  [dbo].[RECEIPT_CONTAINER] b  join [dbo].[RECEIPT_HEADER] a ON a.RECEIPT_ID=b.RECEIPT_ID
WHERE  dateadd(HOUR,24,b.DATE_TIME_STAMP)>=getdate()";
        $data = $this->fetchDataWMS($report1SQL);
        $excelfile1 = $this->generateExcelReport14A1('联谊路仓库收货明细上溯24小时数据',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>------数据:当前时间点上溯24小时联谊路仓库收货明细,本邮件由系统自动发出,请勿直接回复!------------</div>";

        $this->sendReport($to, "联谊路仓库收货明细上溯24小时数据", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 联谊路仓库收货明细上溯24小时数据
    protected function generateExcelReport14A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '收货ID');
        $currentSheet->setCellValue('B'.$m, '物料编码');
        $currentSheet->setCellValue('C'.$m, '物料描述');
        $currentSheet->setCellValue('D'.$m, '收货数量');
        $currentSheet->setCellValue('E'.$m, '收货时间');

        $currentSheet->setCellValue('F'.$m, 'SAP采购单号');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['RECEIPT_ID'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['ITEM'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['ITEM_DESC'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['QUANTITY'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['DATE_TIME_STAMP'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['ERP_ORDER_ID'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 联谊路仓库收货明细上溯24小时数据 结束

    // 报表: 入库失败通知 开始
    function report15(){

        $to = array(
            'maxwell.shi@g-town.com.cn'
        );
        $cc = array(
        );

        $attachment = array();

        $report1SQL = "";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport1A1('已发货48小时无物流信息',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>数据:当前时间点上溯48小时内所有下发联谊路仓库订单明细</div>";

        $this->sendReport($to, "联谊路发货表", $mailConnent,$attachment,$cc);
        exit();

    }
    // 报表: 入库失败通知 结束

    // 报表: 视频卡报表1 开始
    function reportVideoCards1(){

        ini_set('memory_limit', '512M');

        // Rule: 早上9点=09,到下午10点=22 => 时间范围大于08小于23执行:
        $hour = date('H');
        if($hour > 8 && $hour <23){}
        else{
            die();
        }

        $to = array(
//            'maxwell.shi@g-town.com.cn',
//            'molly.li@g-town.com.cn',
//            'dem.he@g-town.com.cn',
//        'irene.zhu@g-town.com.cn',

        'chris.guo@g-town.com.cn',
        'melody.wang@g-town.com.cn',
        'mars.liang@g-town.com.cn',
        'kuijian.bao@g-town.com.cn',
        'lacy.li@g-town.com.cn',
        'steven.shi@g-town.com.cn',
        'tina.wu@g-town.com.cn',
        'abyss.shen@g-town.com.cn',
        'antoine.wei@g-town.com.cn',
        'meepo.zhuang@g-town.com.cn',
            'andy.fang@g-town.com.cn', 'Wendy.li@g-town.com.cn', 'Mario@g-town.com.cn', 'Tank.Tan@g-town.com.cn', 'molly.li@g-town.com.cn', 'julia.zhang@g-town.com.cn', 'maxwell.shi@g-town.com.cn'

            //        'tony@g-town.com.cn',

        );
        $cc = array(
        );

        $attachment = array();

        // attachment 01
        $url = 'http://couponapi.g-town.com.cn/report/voucherCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
        }
        if($data) {
            $excelfile1 = $this->generateExcelReportVideoCards1A1('存券报表', $data);
            $attachment[] = $excelfile1;
        }
        $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;' colspan='12'>存 券 报 表</td></tr><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>商品编号</td><td style='white-space: nowrap;'>商品名称</td><td style='white-space: nowrap;'>平台编码</td><td style='white-space: nowrap;'>平台</td><td style='white-space: nowrap;'>售价</td><td style='white-space: nowrap;'>可用数量</td><td style='white-space: nowrap;'>警戒库存</td><td style='white-space: nowrap;'>前一日销售额</td><td style='white-space: nowrap;'>当日销量</td><td style='white-space: nowrap;'>日均销量</td><td style='white-space: nowrap;'>前一日销量</td><td style='white-space: nowrap;'>累计总销量</td></tr>";
        foreach ($data as $key => $item){
            $mailConnent .= "<tr align='center'><td>".$item['itemCode']."</td>"."<td style='white-space: nowrap;'>".$item['itemName']."</td>"."<td>".$item['channelCode']."</td>"."<td>".$item['channle']."</td>"."<td>".$item['price']."</td>"."<td>".$item['stock']."</td>"."<td>".$item['vigilanceStock']."</td>"."<td>".$item['beforeDaySale']."</td>"."<td>".$item['currDay']."</td>"."<td>".$item['day4Avg']."</td>"."<td>".$item['beforeDay']."</td>"."<td>".$item['totalSale']."</td></tr>";
        }
        $mailConnent .= "</table>";

        // attachment 02
        $url = 'http://couponapi.g-town.com.cn/report/directCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
//            $summary = $arrayData['mergeStock'];
        }
        if($data) {
            $excelfile2 = $this->generateExcelReportVideoCards1A2('直充报表', $data);
            $attachment[] = $excelfile2;
        }
        $mailConnent .= "<table width='100%' border='1' cellpadding='0' cellspacing='0'><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;' colspan='12'>直 充 报 表</td></tr><tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;'>商品编号</td><td style='white-space: nowrap;'>商品名称</td><td style='white-space: nowrap;'>平台编码</td><td style='white-space: nowrap;'>平台</td><td style='white-space: nowrap;'>价格</td><td style='white-space: nowrap;'>实际库存</td><td style='white-space: nowrap;'>当日销量</td><td style='white-space: nowrap;'>警戒库存</td><td style='white-space: nowrap;'>日均销量</td><td style='white-space: nowrap;'>累计2个月总销量</td><td style='white-space: nowrap;'>前一日销售额</td><td style='white-space: nowrap;'>前一日销量</td></tr>";
        foreach ($data as $key => $item){
            $mailConnent .= "<tr align='center'><td>".$item['itemCode']."</td>"."<td style='white-space: nowrap;'>".$item['itemName']."</td>"."<td>".$item['channelCode']."</td>"."<td>".$item['channelName']."</td>"."<td>".$item['price']."</td>"."<td>".$item['stock']."</td>"."<td>".$item['currDay']."</td>"."<td>".$item['vigilanceStock']."</td>"."<td>".$item['dayAvg']."</td>"."<td>".$item['twoMonth']."</td>"."<td>".$item['beforeDaySale']."</td>"."<td>".$item['beforeDay']."</td></tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "券码报表", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 存券报表
    protected function generateExcelReportVideoCards1A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台');
        $currentSheet->setCellValue('D'.$m, '售价');
        $currentSheet->setCellValue('E'.$m, '可用数量');

        $currentSheet->setCellValue('F'.$m, '警戒库存');
        $currentSheet->setCellValue('G'.$m, '前一日销售额');
        $currentSheet->setCellValue('H'.$m, '当日销量');
        $currentSheet->setCellValue('I'.$m, '日均销量');
        $currentSheet->setCellValue('J'.$m, '前一日销量');

        $currentSheet->setCellValue('K'.$m, '累计总销量');
        $currentSheet->setCellValue('L'.$m, '平台编码');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channle'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['price'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['beforeDaySale'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['day4Avg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['totalSale'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['channelCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 直充报表
    protected function generateExcelReportVideoCards1A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台');
        $currentSheet->setCellValue('D'.$m, '价格');
        $currentSheet->setCellValue('E'.$m, '实际库存');

        $currentSheet->setCellValue('F'.$m, '当日销量');
        $currentSheet->setCellValue('G'.$m, '警戒库存');
        $currentSheet->setCellValue('H'.$m, '日均销量');
        $currentSheet->setCellValue('I'.$m, '累计2个月总销量');
        $currentSheet->setCellValue('J'.$m, '前一日销售额');

        $currentSheet->setCellValue('K'.$m, '前一日销量');
        $currentSheet->setCellValue('L'.$m, '平台编码');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channelName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['price'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['twoMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('J'.$cellIndex,$v['beforeDaySale'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('K'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('L'.$cellIndex,$v['channelCode'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $cellIndex++;

        }
//        $currentSheet->setCellValueExplicit('A'.$cellIndex,$summary[0]['key'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $currentSheet->setCellValueExplicit('B'.$cellIndex,$summary[0]['stock'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $cellIndex++;
//        $currentSheet->setCellValueExplicit('A'.$cellIndex,$summary[1]['key'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $currentSheet->setCellValueExplicit('B'.$cellIndex,$summary[1]['stock'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $cellIndex++;
//        $currentSheet->setCellValueExplicit('A'.$cellIndex,$summary[2]['key'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $currentSheet->setCellValueExplicit('B'.$cellIndex,$summary[2]['stock'], \PHPExcel_Cell_DataType::TYPE_STRING);
//        $cellIndex++;

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 视频卡报表1 结束

    // 报表: 视频卡报表2 开始
    function reportVideoCards2(){

        ini_set('memory_limit', '512M');

        // Rule: 早上8点:
        $hour = date('H');
        if($hour == 8){}
        else{
            die();
        }

        $to = array(
            'wallace.ding@g-town.com.cn',
            'Jinx.Fang@g-town.com.cn',
            'mario@g-town.com.cn',
            'maxwell.shi@g-town.com.cn',
            'irene.zhu@g-town.com.cn',
            'meepo.zhuang@g-town.com.cn',
            'kuijian.bao@g-town.com.cn',
            'molly.li@g-town.com.cn',
           // 'andy.fang@g-town.com.cn',
            'tony@g-town.com.cn',
        );
        $cc = array(
        );

        $attachment = array();

        // attachment 01
        $url = 'http://couponapi.g-town.com.cn/newreport/dayrectCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
            sort($data);
        }
        /*$theToday = date('Y-m-d',time());
        // 数据源切换为：SAP:SQL-SERVER.
        import("@.ORG.Util.Autoreportsqlserver");
        $sqlserver  = new Autoreportsqlserver();
        $sqlquery = "select
A.cardcode as channelCode,
A.cardname as channelName,
b.itemcode as itemCode,
b.Dscription as itemName,
cast(sum(b.linetotal)/sum(B.Quantity) as decimal(10,2)) as price,
cast(sum(B.Quantity) as decimal(8,0)) as count,
cast(sum(b.linetotal) as decimal(10,2)) as sale
from ODLN A  JOIN DLN1 B  on a.docentry=b.DocEntry
where
a.cardcode in ('C20113','C20114','C20007','C20120','C20122','C20123','C20071','C20091','C20020','C20101','C20125','C20045','C20033','C20130')
and A.docdate>= dateadd(day,-1,'".$theToday."') and A.docdate < CONVERT(datetime, '".$theToday."') and b.itemcode in  (SELECT ITEMCODE FROM OITM WHERE QryGroup45='Y')
group by A.cardcode,A.cardname,b.itemcode,b.Dscription
order by A.cardcode asc";
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();*/

        if($data) {
            $excelfile1 = $this->generateExcelReportVideoCards2A1('虚拟卡券日报', $data);
            $attachment[] = $excelfile1;
        }
        $mergeArray = Array();
        foreach ($data as $key => $item){
            $mergeArray[$item['channelName']][] = $item;
        }
        $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'>";
        foreach ($mergeArray as $key => $item){
            $mailConnent .= "<tr align='left' style='background-color: #0e9aef'><td style='white-space: nowrap;' colspan='7'>".$key."</td></tr>";
            $mailConnent .= "<tr align='left'><td style='white-space: nowrap;'>商品名称</td><td style='white-space: nowrap;'>售价</td><td style='white-space: nowrap;'>数量</td><td style='white-space: nowrap;'>总金额</td></tr>";
            $sumQty = 0;
            $sumAmount = 0;
            foreach ($item as $inKey => $inItem){
                $mailConnent .= "<tr align='left'>"."<td>".$inItem['itemName']."</td>"."<td>".$inItem['price']."</td>"."<td>".$inItem['count']."</td>"."<td>".$inItem['sale']."</td>"."</tr>";
                $sumQty += $inItem['count'];
                $sumAmount += $inItem['sale'];
            }
            $mailConnent .= "<tr align='left'>"."<td>合计</td>"."<td> </td>"."<td>".$sumQty."</td>"."<td>".$sumAmount."</td>"."</tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "虚拟卡券日报", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 虚拟卡券日报
    protected function generateExcelReportVideoCards2A1($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '平台代码');
        $currentSheet->setCellValue('B'.$m, '平台名称');
        $currentSheet->setCellValue('C'.$m, '商品代码');
        $currentSheet->setCellValue('D'.$m, '商品名称');
        $currentSheet->setCellValue('E'.$m, '售价');

        $currentSheet->setCellValue('F'.$m, '数量');
        $currentSheet->setCellValue('G'.$m, '总金额');

        $cellIndex=2;
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['channelCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['channelName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['price'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['count'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['sale'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $cellIndex++;

        }
        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 视频卡报表2 结束

    // 报表: 视频卡报表3 开始
    function reportVideoCards3(){

        ini_set('memory_limit', '512M');

        $hour = date('H');
        if($hour > 8 && $hour <23){}
        else{
            //die();
        }

        $to = array(
            'maxwell.shi@g-town.com.cn',
        );
        $cc = array(
        );

        $attachment = array();

        // attachment 01
        $url = 'http://10.27.240.231:8199/json/report/videocards.php';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
        }
        if($data) {
            $excelfile1 = $this->generateExcelReportVideoCards3A1('虚拟卡券监控日报', $data);
            $attachment[] = $excelfile1;
        }
        $mailConnent = "";

//        $this->sendReport($to, "虚拟卡券监控日报", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 虚拟卡券监控日报
    protected function generateExcelReportVideoCards3A1($filename,$data){

        // 准备表头数据：
        $titleArray = $data[0]['monthData'];
        foreach ($titleArray as $key => $value){
            $newTitleArray[] = $key;
        }
        $howManyDays = count($newTitleArray);
        $fixedTitleArray = Array(
            'C','D','E','F','G',
            'H','I','J','K','L',
            'M','N','O','P','Q',
            'R','S','T','U','V',
            'W','X','Y','Z','AA',
            'AB','AC','AD','AE','AF',
            'AG',
            'AH','AI','AJ','AK','AL',
            'AM','AN','AO',
        );

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');

        for($i=0;$i<$howManyDays;$i++)
        {
            $currentSheet->setCellValue($fixedTitleArray[$i].$m, $newTitleArray[$i]);
        }

        $currentSheet->setCellValue($fixedTitleArray[$howManyDays].$m, '当月日均销量');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+1].$m, '当日销量');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+2].$m, '前一日销量');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+3].$m, '近7日日均销量');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+4].$m, '产品售价');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+5].$m, '当月累计销量');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+6].$m, '当前库存数');
        $currentSheet->setCellValue($fixedTitleArray[$howManyDays+7].$m, '警戒库存');

        $cellIndex=2;

        $mergeArray = Array();

        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['code'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['name'], \PHPExcel_Cell_DataType::TYPE_STRING);

            $daySUM = 0;
            $dayAVERAGE = 0;
            for($i=0;$i<$howManyDays;$i++)
            {
                $currentSheet->setCellValue($fixedTitleArray[$i].$cellIndex,$v['monthData'][$newTitleArray[$i]] ,\PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $daySUM += $v['monthData'][$newTitleArray[$i]];
            }
            $dayAVERAGE = $daySUM/$howManyDays;
            $dayAVERAGERound = round($dayAVERAGE);

            $sevenDaysTotal=0;
            for($j=1;$j<8;$j++) {
                $sevenDaysTotal += $v['monthData'][$newTitleArray[$howManyDays - $j]];
            }
            $sevenDaysAVERAGE = $sevenDaysTotal/7;
            $sevenDaysAVERAGERound = round($sevenDaysAVERAGE);

            $currentDay = $v['monthData'][$newTitleArray[$howManyDays - 1]];
            $oneDayBefore = $v['monthData'][$newTitleArray[$howManyDays - 2]];

            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays].$cellIndex,$dayAVERAGERound, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+1].$cellIndex,$currentDay, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+2].$cellIndex,$oneDayBefore, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+3].$cellIndex,$sevenDaysAVERAGERound, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+4].$cellIndex,$v['price'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+5].$cellIndex,$daySUM, \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+6].$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $warningStock = ($oneDayBefore*3 + $sevenDaysAVERAGE*5)*1.2;
            $warningStockRound = round($warningStock);

            $currentSheet->setCellValueExplicit($fixedTitleArray[$howManyDays+7].$cellIndex,$warningStockRound, \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            // check the item type:
            //$mergeArray[$v['type']][] = $cellIndex;

            $cellIndex++;

        }

        // 设置背景色
        $currentSheet->getStyle( 'A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
        $currentSheet->getStyle( 'A1')->getFill()->getStartColor()->setARGB('FFFF0000');

        // 合并单元格
        $currentSheet->mergeCells("A1:B1");
        //var_dump($mergeArray);
        //die();

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 视频卡报表3 结束

    // 报表: 视频卡报表3_1 开始
    function reportVideoCards3_1(){

        ini_set('memory_limit', '512M');

        $hour = date('H');
        if($hour > 8 && $hour <23){}
        else{
            //die();
        }

        $to = array(
            'maxwell.shi@g-town.com.cn',
            'molly.li@g-town.com.cn',
//            'dem.he@g-town.com.cn'
        );
        $cc = array(
        );

        $attachment = array();

        // attachment 01
        $url = 'http://10.27.240.127:8199/json/report/videocards3.html';
//        $url = 'http://couponapi.g-town.com.cn/newreport/directCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        if($arrayData['code'] == 0)
        {
            $normalData = $arrayData['data'];
            $mergedData = $arrayData['mergeData'];
        }
        if($normalData) {
            $excelfile1 = $this->generateExcelReportVideoCards3_1A1('直充报表', $normalData, $mergedData);
            $attachment[] = $excelfile1;

            $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;' colspan='12'>直 充 报 表</td></tr>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;'>商品编号</td>
<td style='white-space: nowrap;'>商品名称</td>
<td style='white-space: nowrap;'>平台名称</td>
<td style='white-space: nowrap;'>当日销量</td>
<td style='white-space: nowrap;'>前一日销量</td>
<td style='white-space: nowrap;'>近7日日均销量</td>
<td style='white-space: nowrap;'>当月累计销量</td>
<td style='white-space: nowrap;'>当前库存数</td>
<td style='white-space: nowrap;'>警戒库存</td></tr>";
            // $normalData
            foreach ($normalData as $key => $item) {
                $mailConnent .= "<tr align='center'>
<td>" . $item['itemCode'] . "</td>" .
                    "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                    "<td>" . $item['channelName'] . "</td>" ;

                if ($item['currDay'] > $item['beforeDay'] * 2) {
                    $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
                } else {
                    $mailConnent .= "<td>" . $item['currDay'] . "</td>";
                }

                // send out sms
                if ($item['currDay'] > $item['beforeDay'] * 3) {
                    foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                        $smsParams = array(
                            'phone' => $rItem,
                            'content' => '(' . $item['itemName'] . ')在' . $item['channelName'] . '平台销量已超前一日3倍，请关注！',
                            'sysCode' => $this->smsSysCode);
                        $this->requestService($this->smsApi, $smsParams);

                    }
                }

                $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                    "<td>" . $item['7dayAvg'] . "</td>" .
                    "<td>" . $item['currMonth'] . "</td>" .
                    "<td>" . $item['stock'] . "</td>" .
                    "<td>" . $item['vigilanceStock'] . "</td>" .
                    "</tr>";
            }

            // $mergedData
            if(is_array($mergedData)){
                foreach ($mergedData['rows'] as $key => $item){
                    $mailConnent .= "<tr align='center'>
<td>" . $item['itemCode'] . "</td>" .
                        "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                        "<td>" . $item['channelName'] . "</td>" ;

                    if ($item['currDay'] > $item['beforeDay'] * 2) {
                        $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
                    } else {
                        $mailConnent .= "<td>" . $item['currDay'] . "</td>";
                    }

                    // send out sms
                    if ($item['currDay'] > $item['beforeDay'] * 3) {
                        foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                            $smsParams = array(
                                'phone' => $rItem,
                                'content' => '(' . $item['itemName'] . ')在' . $item['channelName'] . '平台销量已超前一日3倍，请关注！',
                                'sysCode' => $this->smsSysCode);
                            $this->requestService($this->smsApi, $smsParams);

                        }
                    }

                    $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                        "<td>" . $item['7dayAvg'] . "</td>" .
                        "<td>" . $item['currMonth'] . "</td>";
                    if($key == 0) {
                        $mailConnent .="<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['stock'] . "</td>" .
                        "<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['vigilanceStock'] . "</td>";
                        }else{
                        $mailConnent .="</tr>";
                    }

                }
            }


            $mailConnent .= "</table>";

        }

        // attachment 02
        $url = 'http://couponapi.g-town.com.cn/newreport/voucherCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
        }
        if($data) {
            $excelfile2 = $this->generateExcelReportVideoCards3_1A2('存券报表', $data);
            $attachment[] = $excelfile2;
        }
        $mailConnent .= "<table width='100%' border='1' cellpadding='0' cellspacing='0'>
<tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;' colspan='12'>存 券 报 表</td></tr>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;'>商品编号</td>
<td style='white-space: nowrap;'>商品名称</td>
<td style='white-space: nowrap;'>平台名称</td>
<td style='white-space: nowrap;'>当日销量</td>
<td style='white-space: nowrap;'>前一日销量</td>
<td style='white-space: nowrap;'>近7日日均销量</td>
<td style='white-space: nowrap;'>当月累计销量</td>
<td style='white-space: nowrap;'>当前库存数</td>
<td style='white-space: nowrap;'>警戒库存</td></tr>";
        foreach ($data as $key => $item) {
            $mailConnent .= "<tr align='center'>
<td>" . $item['itemCode'] . "</td>" .
                "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                "<td>" . $item['channle'] . "</td>" ;

            if ($item['currDay'] > $item['beforeDay'] * 2) {
                $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
            } else {
                $mailConnent .= "<td>" . $item['currDay'] . "</td>";
            }

            // send out sms
            if ($item['currDay'] > $item['beforeDay'] * 3) {
                foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                    $smsParams = array(
                        'phone' => $rItem,
                        'content' => '(' . $item['itemName'] . ')在' . $item['channelName'] . '平台销量已超前一日3倍，请关注！',
                        'sysCode' => $this->smsSysCode);
                    $this->requestService($this->smsApi, $smsParams);

                }
            }

            $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                "<td>" . $item['7dayAvg'] . "</td>" .
                "<td>" . $item['currMonth'] . "</td>" .
                "<td>" . $item['stock'] . "</td>" .
                "<td>" . $item['vigilanceStock'] . "</td>" .
                "</tr>";
        }
        $mailConnent .= "</table>";

        $this->sendReport($to, "虚拟卡券监控-每小时更新:增加平台名称列", $mailConnent,$attachment,$cc);
        exit();

    }
    // Attachment: 虚拟卡券监控日报
    // Attachment: 直充报表
    protected function generateExcelReportVideoCards3_1A1($filename,$normalData,$mergedData){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台名称');
        $currentSheet->setCellValue('D'.$m, '当日销量');
        $currentSheet->setCellValue('E'.$m, '前一日销量');
        $currentSheet->setCellValue('F'.$m, '近7日日均销量');

        $currentSheet->setCellValue('G'.$m, '当月累计销量');
        $currentSheet->setCellValue('H'.$m, '当前库存数');
        $currentSheet->setCellValue('I'.$m, '警戒库存');

        $cellIndex=2;
        // $normalData
        foreach ($normalData as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channelName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            if($v['currDay'] >= $v['beforeDay']*2 && $v['currDay'] != 0)
            {
                // 设置背景色
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->getStartColor()->setARGB('FFFF0000');
            }

            $cellIndex++;

        }
        // $mergedData
        /*
        $mergedDataStart = $cellIndex;
        if(is_array($mergedData)){
            foreach ($mergedData['rows'] as $k => $v){
                $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('C'.$cellIndex,'', \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

                $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

                if($v['currDay'] >= $v['beforeDay']*2 && $v['currDay'] != 0)
                {
                    // 设置背景色
                    $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->getStartColor()->setARGB('FFFF0000');
                }

                $cellIndex++;
            }
        }
        $mergedDataEnd = $cellIndex-1;
        //合并单元格：
        $currentSheet->mergeCells("H".$mergedDataStart.":H".$mergedDataEnd);
        $currentSheet->mergeCells("I".$mergedDataStart.":I".$mergedDataEnd);
        $currentSheet->setCellValueExplicit('H'.$mergedDataStart,$mergedData['megeData']['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
        $currentSheet->setCellValueExplicit('I'.$mergedDataStart,$mergedData['megeData']['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
        */

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 存券报表
    protected function generateExcelReportVideoCards3_1A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台名称');
        $currentSheet->setCellValue('D'.$m, '当日销量');
        $currentSheet->setCellValue('E'.$m, '前一日销量');
        $currentSheet->setCellValue('F'.$m, '近7日日均销量');

        $currentSheet->setCellValue('G'.$m, '当月累计销量');
        $currentSheet->setCellValue('H'.$m, '当前库存数');
        $currentSheet->setCellValue('I'.$m, '警戒库存');

        $cellIndex=2;
        // $normalData
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channle'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            if($v['currDay'] > $v['beforeDay']*2)
            {
                // 设置背景色
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->getStartColor()->setARGB('FFFF0000');
            }

            $cellIndex++;

        }

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 视频卡报表3_1 结束

    // 报表: 视频卡报表3_2 开始

    function reportVideoCards3_2(){

        ini_set('memory_limit', '512M');
        ignore_user_abort();
        ini_set('max_execution_time', '0');
        set_time_limit(0);

        $hour = date('H');
        if($hour > 8 && $hour <23){}
        else{
            die();
        }

        $to1 = array(
            'vicki.li@g-town.com.cn',
            'Jinx.Fang@g-town.com.cn',
            'Shermie.Xu@g-town.com.cn',

            'wallace.ding@g-town.com.cn',
            'john.chen@g-town.com.cn',
            'irene.zhu@g-town.com.cn',
            'Shermie.Xu@g-town.com.cn',
//            'chris.guo@g-town.com.cn',
            'melody.wang@g-town.com.cn'

           // 'maxwell.shi@g-town.com.cn'

        );

        $to2 = array(
            //'Jinx.Fang@g-town.com.cn',
            'Shermie.Xu@g-town.com.cn',
            'mars.liang@g-town.com.cn',
            'kuijian.bao@g-town.com.cn',
            'lacy.li@g-town.com.cn',
            'steven.shi@g-town.com.cn',
            'tina.wu@g-town.com.cn',
            'abyss.shen@g-town.com.cn',

            //'maxwell.shi@g-town.com.cn'

        );

        $to3 = array(
            'antoine.wei@g-town.com.cn',
            'meepo.zhuang@g-town.com.cn',
            'Shermie.Xu@g-town.com.cn',
           // 'andy.fang@g-town.com.cn',
            'tony@g-town.com.cn',
            'Wendy.li@g-town.com.cn',
            'eddy.chen@g-town.com.cn',
            'Mario@g-town.com.cn'

            //'maxwell.shi@g-town.com.cn'

        );

        $to4 = array(
            'Shermie.Xu@g-town.com.cn',
            'molly.li@g-town.com.cn',
            //'julia.zhang@g-town.com.cn',
            'Yanyan.Ding@g-town.com.cn',
            'meepo.zhuang@g-town.com.cn',
            'Vicki.Li@g-town.com.cn'
            //'maxwell.shi@g-town.com.cn'

        );

        /*$to = array(
            'vicki.li@g-town.com.cn',
            'wallace.ding@g-town.com.cn',
            'john.chen@g-town.com.cn',
            'irene.zhu@g-town.com.cn',
            'chris.guo@g-town.com.cn',
            'melody.wang@g-town.com.cn',

            'mars.liang@g-town.com.cn',
            'kuijian.bao@g-town.com.cn',
            'lacy.li@g-town.com.cn',
            'steven.shi@g-town.com.cn',
            'tina.wu@g-town.com.cn',
            'abyss.shen@g-town.com.cn',

            'antoine.wei@g-town.com.cn',
            'meepo.zhuang@g-town.com.cn',
            'andy.fang@g-town.com.cn',
            'tony@g-town.com.cn',
            'Wendy.li@g-town.com.cn',
            'Mario@g-town.com.cn',

            'molly.li@g-town.com.cn',
            'julia.zhang@g-town.com.cn',
            'maxwell.shi@g-town.com.cn'

        );*/

        $cc = array(
        );

        $attachment = array();
        $mergedData = array();

        // attachment 00
        $url = 'http://couponapi.g-town.com.cn/newreport/directCoupon';
//        $url = 'http://couponapi.g-town.com.cn/newreport/testdirectCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        if($arrayData['code'] == 0)
        {
            $normalData = $arrayData['data'];
            $mergedData = $arrayData['mergeData'];
        }
        if($normalData) {
            $excelfile1 = $this->generateExcelReportVideoCards3_1A1('平台库存监控报表', $normalData, $mergedData);
            $attachment[] = $excelfile1;

            $mailConnent = "<table width='100%' border='1' cellpadding='0' cellspacing='0'>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;' colspan='12'>平台库存监控报表</td></tr>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;'>商品编号</td>
<td style='white-space: nowrap;'>商品名称</td>
<td style='white-space: nowrap;'>平台名称</td>
<td style='white-space: nowrap;'>当日销量</td>
<td style='white-space: nowrap;'>前一日销量</td>
<td style='white-space: nowrap;'>近7日日均销量</td>
<td style='white-space: nowrap;'>当月累计销量</td>
<td style='white-space: nowrap;'>当前库存数</td>
<td style='white-space: nowrap;'>警戒库存</td></tr>";
            // $normalData
            foreach ($normalData as $key => $item) {
                $mailConnent .= "<tr align='left'>
<td>" . $item['itemCode'] . "</td>" .
                    "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                    "<td>" . $item['channelName'] . "</td>" ;

                if ($item['currDay'] >= $item['beforeDay'] * 2 && $item['currDay'] != 0) {
                    $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
                } else {
                    $mailConnent .= "<td>" . $item['currDay'] . "</td>";
                }

                $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                    "<td>" . $item['7dayAvg'] . "</td>" .
                    "<td>" . $item['currMonth'] . "</td>" .
                    "<td>" . $item['stock'] . "</td>" .
                    "<td>" . $item['vigilanceStock'] . "</td>" .
                    "</tr>";
            }

            // $mergedData
            /*if(is_array($mergedData)){
                foreach ($mergedData['rows'] as $key => $item){
                    $mailConnent .= "<tr align='left'>
<td>" . $item['itemCode'] . "</td>" .
                        "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>";

                    if ($item['currDay'] >= $item['beforeDay'] * 2 && $item['currDay'] != 0) {
                        $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
                    } else {
                        $mailConnent .= "<td>" . $item['currDay'] . "</td>";
                    }

                    $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                        "<td>" . $item['7dayAvg'] . "</td>" .
                        "<td>" . $item['currMonth'] . "</td>";
                    if($key == 0) {
                        $mailConnent .="<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['stock'] . "</td>" .
                            "<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['vigilanceStock'] . "</td>";
                    }else{
                        $mailConnent .="</tr>";
                    }

                }
            }*/


            $mailConnent .= "</table>";

        }

        // attachment 01
//        $url = 'http://10.27.240.121:8199/json/report/videocards3_2data.php';
        $url = 'http://couponapi.g-town.com.cn/newreport/mergedirectCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
        }
        if($data)
        {
            $excelfile1 = $this->generateExcelReportVideoCards3_2A1('产品库存监控报表', $data,$mergedData);
            $attachment[] = $excelfile1;

            $mailConnent .= "<table width='100%' border='1' cellpadding='0' cellspacing='0'>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;' colspan='12'>产品库存监控报表</td></tr>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;'>商品编号</td>
<td style='white-space: nowrap;'>商品名称</td>
<td style='white-space: nowrap;'>平台名称</td>
<td style='white-space: nowrap;'>当日销量</td>
<td style='white-space: nowrap;'>前一日销量</td>
<td style='white-space: nowrap;'>近7日日均销量</td>
<td style='white-space: nowrap;'>当月累计销量</td>
<td style='white-space: nowrap;'>当前库存数</td>
<td style='white-space: nowrap;'>警戒库存</td></tr>";

            foreach ($this->checkedCards as $k => $items) {
                //去爱奇艺
                if($k != 'IQIYI-SEASON' && $k != 'IQIYI-YEAR' && $k != 'IQIYI-MONTH'){
                $i = 0;
                foreach ($items as $code => $name) {
                    $i++;
                    if ($i == 1) {
                        $mailConnent .= "<tr style='white-space: nowrap;' align='left'><td>" . $code . "</td><td>" . $name . "</td><td>" . $this->platformName[$code] . "</td>";

                        if ($data[$k]['currDay'] >= $data[$k]['beforeDay'] * 2 && $data[$k]['currDay'] != 0) {
                            $mailConnent .= "<td rowspan='" . count($items) . "' style='background-color: #ff0000;'>" . $data[$k]['currDay'] . "</td>";
                        } else {
                            $mailConnent .= "<td rowspan='" . count($items) . "'>" . $data[$k]['currDay'] . "</td>";
                        }

                        // send out sms : 在这里就只发送一条SMS！
                        if (($data[$k]['currDay'] >= $data[$k]['stock']/8) && $data[$k]['currDay'] != 0) {
                            // check data:
                            $today = date('Y-m-d');
                            $vcardsSmsCheckModel = D('vcardsSmsCheck');
                            $r = $vcardsSmsCheckModel->where("card_name='$k'AND date='$today'")->find();
                            if($r)
                            {
                                if($r['count'] < 3) {
                                    $saveData = array('count' => $r['count'] + 1);
                                    $vcardsSmsCheckModel->where("id={$r['id']}")->save($saveData);
                                    // send out sms : ??? 01
                                    foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                                        $smsParams = array(
                                            'phone' => $rItem,
                                            'content' => '(' . $this->checkedCardsCNName[$k] . ')销量已大于当前库存数1/8，请关注！',
                                            'sysCode' => $this->smsSysCode);
                                        $this->requestService($this->smsApi, $smsParams);

                                    }

                                }else{
                                }
                            }
                            else {
                                $vcardsSmsCheckModel->add(array('card_name' => $k, 'date' => $today, 'count' => 1));
                            }

                        }

                        $mailConnent .= "<td rowspan='" . count($items) . "'>" . $data[$k]['beforeDay'] . "</td><td rowspan='" . count($items) . "'>" . $data[$k]['7dayAvg'] . "</td><td rowspan='" . count($items) . "'>" . $data[$k]['currMonth'] . "</td><td rowspan='" . count($items) . "'>" . $data[$k]['stock'] . "</td><td rowspan='" . count($items) . "'>" . $data[$k]['vigilanceStock'] . "</td></tr>";
                    } else {
                        $mailConnent .= "<tr style='white-space: nowrap;' align='left'><td>" . $code . "</td><td>" . $name . "</td><td>" . $this->platformName[$code] . "</td></tr>";
                    }

                    // send out sms : 因为是合并信息，在这里发送SMS，所有合并元素都会被发送！

                }

                }
            }

            // $mergedData
            if(is_array($mergedData)){
                foreach ($mergedData['rows'] as $key => $item){
                    $mailConnent .= "<tr align='left'>
<td>" . $item['itemCode'] . "</td>" .
                        "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                        "<td>" . $item['channelName'] . "</td>" ;

                    if ($item['currDay'] >= $item['beforeDay'] * 2 && $item['currDay'] != 0) {
                        $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
                    } else {
                        $mailConnent .= "<td>" . $item['currDay'] . "</td>";
                    }

                    // send out sms
                    /*if (($item['currDay'] >= $item['stock']/8) && $item['currDay'] != 0) {
                        foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                            $smsParams = array(
                                'phone' => $rItem,
                                'content' => '(' . $item['itemName'] . ')在' . $item['channelName'] . '平台销量已超前一日3倍，请关注！',
                                'sysCode' => $this->smsSysCode);
                            $this->requestService($this->smsApi, $smsParams);

                        }
                    }*/

                    $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                        "<td>" . $item['7dayAvg'] . "</td>" .
                        "<td>" . $item['currMonth'] . "</td>";
                    if($key == 0) {
                        $mailConnent .="<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['stock'] . "</td>" .
                            "<td rowspan='" . count($mergedData['rows']) . "'>" . $mergedData['megeData']['vigilanceStock'] . "</td>";
                    }else{
                        $mailConnent .="</tr>";
                    }

                }
            }

            $mailConnent .= "</table>";

        }

        // attachment 02
//        $url = 'http://10.27.240.121:8199/json/report/voucher_coupon.html';
        $url = 'http://couponapi.g-town.com.cn/newreport/voucherCoupon';
//        $url = 'http://couponapi.g-town.com.cn/newreport/testvoucherCoupon';
        $jsonData = $this->http($url,'','GET');
        $arrayData = json_decode($jsonData,true);
        $data = '';
        if($arrayData['code'] == 0)
        {
            $data = $arrayData['data'];
        }
        if($data) {
            $excelfile2 = $this->generateExcelReportVideoCards3_2A2('存券报表', $data);
            $attachment[] = $excelfile2;
        }
        $mailConnent .= "<table width='100%' border='1' cellpadding='0' cellspacing='0'>
<tr align='center' style='background-color: #0e9aef'><td style='white-space: nowrap;' colspan='12'>存 券 报 表</td></tr>
<tr align='center' style='background-color: #0e9aef'>
<td style='white-space: nowrap;'>商品编号</td>
<td style='white-space: nowrap;'>商品名称</td>
<td style='white-space: nowrap;'>平台名称</td>
<td style='white-space: nowrap;'>当日销量</td>
<td style='white-space: nowrap;'>前一日销量</td>
<td style='white-space: nowrap;'>近7日日均销量</td>
<td style='white-space: nowrap;'>当月累计销量</td>
<td style='white-space: nowrap;'>当前库存数</td>
<td style='white-space: nowrap;'>警戒库存</td></tr>";
        foreach ($data as $key => $item) {
            $mailConnent .= "<tr align='left'>
<td>" . $item['itemCode'] . "</td>" .
                "<td style='white-space: nowrap;'>" . $item['itemName'] . "</td>".
                "<td>" . $item['channle'] . "</td>" ;

            if ($item['currDay'] >= $item['beforeDay'] * 2 && $item['currDay'] !=0) {
                $mailConnent .= "<td style='background-color: #ff0000;'>" . $item['currDay'] . "</td>";
            } else {
                $mailConnent .= "<td>" . $item['currDay'] . "</td>";
            }

            // send out sms
            if (($item['currDay'] >= $item['stock']/8) && $item['currDay'] != 0) {
                // check data:
                $today = date('Y-m-d');
                $vcardsSmsCheckModel = D('vcardsSmsCheck');
                $r = $vcardsSmsCheckModel->where("card_name='".$item['itemCode']."'AND date='$today'")->find();
                if($r){
                    if($r['count'] < 3) {
                        $saveData = array('count' => $r['count'] + 1);
                        $vcardsSmsCheckModel->where("id={$r['id']}")->save($saveData);
                        // send out sms : ??? 02
                        foreach ($this->videoCardsSmsReceiver as $rKey => $rItem) {

                            $smsParams = array(
                                'phone' => $rItem,
                                'content' => '(' . $item['itemName'] . ')销量已大于当前库存数1/8，请关注！',
                                'sysCode' => $this->smsSysCode);
                            $this->requestService($this->smsApi, $smsParams);

                        }
                    }else{
                    }
                }else{
                    $vcardsSmsCheckModel->add(array('card_name' => $item['itemCode'], 'date' => $today, 'count' => 1));
                }

            }

            $mailConnent .= "<td>" . $item['beforeDay'] . "</td>" .
                "<td>" . $item['7dayAvg'] . "</td>" .
                "<td>" . $item['currMonth'] . "</td>" .
                "<td>" . $item['stock'] . "</td>" .
                "<td>" . $item['vigilanceStock'] . "</td>" .
                "</tr>";
        }
        $mailConnent .= "</table>";

        //file_put_contents("emailServer.txt","1",FILE_APPEND);
        $this->sendReport($to1, "虚拟卡券报表-b1", $mailConnent,$attachment,$cc);
        //file_put_contents("emailServer.txt",$r,FILE_APPEND);
        //file_put_contents("emailServer.txt","-1",FILE_APPEND);
        sleep(2);
        //file_put_contents("emailServer.txt","2",FILE_APPEND);
        $this->sendReport($to2, "虚拟卡券报表-b2", $mailConnent,$attachment,$cc);
        //file_put_contents("emailServer.txt",$r,FILE_APPEND);
        //file_put_contents("emailServer.txt","-2",FILE_APPEND);
        sleep(2);
        //file_put_contents("emailServer.txt","3",FILE_APPEND);
        $this->sendReport($to3, "虚拟卡券报表-b3", $mailConnent,$attachment,$cc);
        //file_put_contents("emailServer.txt",$r,FILE_APPEND);
        //file_put_contents("emailServer.txt","-3",FILE_APPEND);
        sleep(2);
        //file_put_contents("emailServer.txt","4",FILE_APPEND);
        $this->sendReport($to4, "虚拟卡券报表-b4", $mailConnent,$attachment,$cc);
        //file_put_contents("emailServer.txt",$r,FILE_APPEND);
        //file_put_contents("emailServer.txt","-4",FILE_APPEND);

        exit();

    }
    public function tstEmailServer(){
        $to = array(
            'maxwell.shi@g-town.com.cn'
        );
        $mailConnent = "tstEmailServer";
        $attachment = array();
        $cc = array();
        $r = $this->sendReport($to, "tstEmailServer", $mailConnent,$attachment,$cc);
        file_put_contents("emailServer.txt",$r,FILE_APPEND);
        exit();
    }
    // Attachment: 虚拟卡券监控时报
    // Attachment: 产品库存监控报表
    protected function generateExcelReportVideoCards3_2A1($filename,$data,$mergedData){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台名称');
        $currentSheet->setCellValue('D'.$m, '当日销量');
        $currentSheet->setCellValue('E'.$m, '前一日销量');
        $currentSheet->setCellValue('F'.$m, '近7日日均销量');

        $currentSheet->setCellValue('G'.$m, '当月累计销量');
        $currentSheet->setCellValue('H'.$m, '当前库存数');
        $currentSheet->setCellValue('I'.$m, '警戒库存');

        $cellIndex=2;
        if(is_array($this->checkedCards)){
            foreach ($this->checkedCards as $k => $items) {

                if($k != 'IQIYI-SEASON' && $k != 'IQIYI-YEAR' && $k != 'IQIYI-MONTH'){

                $mergedDataStart = $cellIndex;
                foreach ($items as $code => $name) {
                    $currentSheet->setCellValueExplicit('A' . $cellIndex, $code, \PHPExcel_Cell_DataType::TYPE_STRING);
                    $currentSheet->setCellValueExplicit('B' . $cellIndex, $name, \PHPExcel_Cell_DataType::TYPE_STRING);
                    $currentSheet->setCellValueExplicit('C' . $cellIndex, $this->platformName[$code], \PHPExcel_Cell_DataType::TYPE_STRING);
                    $cellIndex++;
                }
                //合并单元格
                $mergedDataEnd = $cellIndex - 1;
                $currentSheet->mergeCells("D" . $mergedDataStart . ":D" . $mergedDataEnd);
                $currentSheet->mergeCells("E" . $mergedDataStart . ":E" . $mergedDataEnd);
                $currentSheet->mergeCells("F" . $mergedDataStart . ":F" . $mergedDataEnd);
                $currentSheet->mergeCells("G" . $mergedDataStart . ":G" . $mergedDataEnd);
                $currentSheet->mergeCells("H" . $mergedDataStart . ":H" . $mergedDataEnd);
                $currentSheet->mergeCells("I" . $mergedDataStart . ":I" . $mergedDataEnd);

                //填充数据
                $currentSheet->setCellValueExplicit('D' . $mergedDataStart, $data[$k]['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('E' . $mergedDataStart, $data[$k]['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('F' . $mergedDataStart, $data[$k]['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('G' . $mergedDataStart, $data[$k]['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('H' . $mergedDataStart, $data[$k]['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('I' . $mergedDataStart, $data[$k]['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

                //改变背景色
                if ($data[$k]['currDay'] >= $data[$k]['beforeDay'] * 2 && $data[$k]['currDay'] != 0) {
                    // 设置背景色
                    $currentSheet->getStyle('D' . $mergedDataStart)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $currentSheet->getStyle('D' . $mergedDataStart)->getFill()->getStartColor()->setARGB('FFFF0000');
                }

                }

            }
        }

        // $mergedData
        $mergedDataStart = $cellIndex;
        if(is_array($mergedData)){
            foreach ($mergedData['rows'] as $k => $v){
                $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channelName'], \PHPExcel_Cell_DataType::TYPE_STRING);
                $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

                $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

                if($v['currDay'] >= $v['beforeDay']*2 && $v['currDay'] != 0)
                {
                    // 设置背景色
                    $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->getStartColor()->setARGB('FFFF0000');
                }

                $cellIndex++;
            }
        }
        $mergedDataEnd = $cellIndex-1;
        //合并单元格：
        $currentSheet->mergeCells("H".$mergedDataStart.":H".$mergedDataEnd);
        $currentSheet->mergeCells("I".$mergedDataStart.":I".$mergedDataEnd);
        $currentSheet->setCellValueExplicit('H'.$mergedDataStart,$mergedData['megeData']['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
        $currentSheet->setCellValueExplicit('I'.$mergedDataStart,$mergedData['megeData']['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // Attachment: 存券报表
    protected function generateExcelReportVideoCards3_2A2($filename,$data){

        Vendor('Excel.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $currentSheet = $objPHPExcel->getSheet(0);
        $m = 1;
        $currentSheet->setCellValue('A'.$m, '商品编号');
        $currentSheet->setCellValue('B'.$m, '商品名称');
        $currentSheet->setCellValue('C'.$m, '平台名称');
        $currentSheet->setCellValue('D'.$m, '当日销量');
        $currentSheet->setCellValue('E'.$m, '前一日销量');
        $currentSheet->setCellValue('F'.$m, '近7日日均销量');

        $currentSheet->setCellValue('G'.$m, '当月累计销量');
        $currentSheet->setCellValue('H'.$m, '当前库存数');
        $currentSheet->setCellValue('I'.$m, '警戒库存');

        $cellIndex=2;
        // $normalData
        foreach ($data as $k => $v){

            $currentSheet->setCellValueExplicit('A'.$cellIndex,$v['itemCode'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('B'.$cellIndex,$v['itemName'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('C'.$cellIndex,$v['channle'], \PHPExcel_Cell_DataType::TYPE_STRING);
            $currentSheet->setCellValueExplicit('D'.$cellIndex,$v['currDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('E'.$cellIndex,$v['beforeDay'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('F'.$cellIndex,$v['7dayAvg'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            $currentSheet->setCellValueExplicit('G'.$cellIndex,$v['currMonth'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('H'.$cellIndex,$v['stock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $currentSheet->setCellValueExplicit('I'.$cellIndex,$v['vigilanceStock'], \PHPExcel_Cell_DataType::TYPE_NUMERIC);

            if($v['currDay'] >= $v['beforeDay']*2 && $v['currDay'] !=0)
            {
                // 设置背景色
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                $currentSheet->getStyle( 'D'.$cellIndex)->getFill()->getStartColor()->setARGB('FFFF0000');
            }

            $cellIndex++;

        }

        $timeTag = date('Ymd_Hi');
        $filename = mb_convert_encoding($filename,'gbk','utf-8');
        $name = $filename.'_'.$timeTag.'.xls';
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        $objWriter->save($fullPath);
        return $fullPath;

    }
    // 报表: 视频卡报表3_2 结束

    // 报表: 视频卡报表3_3 开始
    function reportVideoCards3_3(){

        ini_set('memory_limit', '512M');

        // Rule: 早上8点:
        $hour = date('H');
        if ($hour == 8) {
        } else {
            die();
        }

        $to = array(
           // 'eddy.chen@g-town.com.cn',
            //'Jinx.Fang@g-town.com.cn',
            'wallace.ding@g-town.com.cn',
            'molly.li@g-town.com.cn',
            'Shermie.Xu@g-town.com.cn',
            //'irene.zhu@g-town.com.cn',
            //'abyss@g-town.com.cn',
           // 'kuijian.bao@g-town.com.cn',
           'Yanyan.Ding@g-town.com.cn',
           // 'sophie.li@g-town.com.cn',
            //'andy.fang@g-town.com.cn',
            'tony@g-town.com.cn'
        );
        $cc = array(
        );

        $attachment = array();

        $png = $this->attachedPng('tencent');
        $attachment[] = $png;
        $png = $this->attachedPng('youku');
        $attachment[] = $png;
        $png = $this->attachedPng('iqiyi');
        $attachment[] = $png;
        $png = $this->attachedPng('cmbchina');
        $attachment[] = $png;
        $png = $this->attachedPng('boc');
        $attachment[] = $png;
        $png = $this->attachedPng('all');
        $attachment[] = $png;

//        $html = $this->attachedHtml('tst');
//        $attachment[] = $html;

        $mailConnent = "";

        if(is_array($attachment)) {
            if(count($attachment) == 6){
                $this->sendReport($to, "视频卡销售趋势", $mailConnent, $attachment, $cc);
            }
        }

        exit();

    }
    // Attachment: PNG
    protected function attachedPng($cor){

        $timeTag = date('Ymd');

        $name = $cor.'_'.$timeTag.'.png';

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        return $fullPath;

    }
    // Attachment: HTML
    protected function attachedHtml($cor){

        $timeTag = date('Ymd');

        $name = $cor.'_'.$timeTag.'.html';

        $file = 'Uploads/autoreport/'.date('Ymd');
        if(!file_exists($file)){
            mkdir($file);
        }

        $fullPath = $file.'/'.$name;
        return $fullPath;

    }
    function reportVideoCards3_3CreateFolder()
    {

        ini_set('memory_limit', '512M');

        // Rule: 早上6点:
        $hour = date('H');
        if ($hour == 6) {
        } else {
//            die();
        }

        // 每天早6点创建目录(PHP)，7点生成图片(PY)，8点发送折线图报表(PHP)
        $png = $this->attachedPng('tencent');

    }
    // 报表: 视频卡报表3_3 结束

    // 一个bat多条命令: E: & cd E:\Sites\gtoa\ & "php.exe" cli.php AutoReport/tstBat1/ & "php.exe" cli.php AutoReport/tstBat2/
    function tstBat1(){
        echo __FUNCTION__;
    }

    function tstBat2(){
        echo  __FUNCTION__;
    }

    // 报表: Template 开始
    function report_x(){

        $to = array(
            'maxwell.shi@g-town.com.cn'
        );
        $cc = array(
        );

        $attachment = array();

        $report1SQL = "";
        $data = $this->fetchData($report1SQL);
        $excelfile1 = $this->generateExcelReport1A1('已发货48小时无物流信息',$data);
        $attachment[] = $excelfile1;

        $mailConnent = "<div>数据:当前时间点上溯48小时内所有下发联谊路仓库订单明细</div>";

        $this->sendReport($to, "联谊路发货表", $mailConnent,$attachment,$cc);
        exit();

    }
    // 报表: Template 结束

    // 通用开始
    // Template
    protected  function sendReport($tomail,$title,$connent,$attachment=array(),$cc=array()){
        //邮件模版    内容替换%connent%
        $mailmodel =<<<html
<div>%connent%</div>
html;
        $connent = str_replace("%connent%", $connent ,$mailmodel);
        return $this->sendEamilAutoReport($tomail, $title, $connent,$attachment,$cc);
    }
    // Fetch Data
    protected function fetchData($sqlquery){
        import("@.ORG.Util.Autoreportsqlserver");
        $sqlserver  = new Autoreportsqlserver();
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();
        return $data;
    }
    // Fetch Data
    protected function fetchDataWMS($sqlquery){
        import("@.ORG.Util.Autoreportwms");
        $sqlserver  = new Autoreportwms();
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();
        return $data;
    }
    // Fetch Data
    protected function fetchDataTTX($sqlquery){
        import("@.ORG.Util.Autoreportttx");
        $sqlserver  = new Autoreportttx();
        $data = $sqlserver->query($sqlquery);
        $sqlserver->close();
        return $data;
    }
    // Send Email
    protected function sendEamilAutoReport($toMail, $title, $content, $Attachment,$cc=array())
    {
        $respon = array(
            'code' => 0,
            'msg' => ''
        );


        import("@.ORG.Util.send");
        $mail = new \PHPMailer(true);

        $mail->CharSet = "UTF-8";
        $mail->IsSMTP();
        // 这里指定字符集！解决中文乱码问题
        $mail -> Encoding = "base64";
        $mail->SMTPAuth = true;
        $mail->SMTPSecure = "tls";
        $mail->Host = "192.168.1.44";
        $mail->Port = 587;
        $mail->Username = 'oa@g-town.com.cn';
        $mail->Password ='Public321';
        $mail->SetFrom($mail->Username, '海牙湾');
        //$mail->AddReplyTo($mail->Username , '测试');
        //if(session('emp_no'))
            //$mail->AddReplyTo(session('emp_no').'@g-town.com.cn',session('emp_no'));
        // 设置邮件回复人地址和名称
        $mail->Subject = '【'.$title.' 】'; // 设置邮件标题
        $mail->AltBody = "为了查看该邮件，请切换到支持 HTML 的邮件客户端";

        // 可选项，向下兼容考虑
        $mail->MsgHTML($content); // 设置邮件内容

        if($toMail) {
            foreach ($toMail as $to) {
                $mail->AddAddress($to);
            }
        }

        if($cc) {
            foreach ($cc as $c) {
                $mail->AddCC($c);
            }
        }

        if ($Attachment){
            foreach ($Attachment as $a) {
                //$trueName = end(explode("/",$a));
                //var_dump($trueName);die();
                $mail->AddAttachment($a); // 附件
            }
        }

        //$ats = $mail->GetAttachments();
        //var_dump($ats);die();

        //exit();

        if (! $mail->Send()) {

            $respon['code'] = - 1;
            $respon['msg'] = $mail->ErrorInfo;
        } else {
            $respon['msg'] = "邮件发送成功！";
        }

        return $respon;
    }

    function http($url, $params, $method = 'GET', $header = array(), $multi = false) {
        $opts = array(CURLOPT_TIMEOUT => 30, CURLOPT_RETURNTRANSFER => 1, CURLOPT_SSL_VERIFYPEER => false, CURLOPT_SSL_VERIFYHOST => false, CURLOPT_HTTPHEADER => $header);

        /* 根据请求类型设置特定参数 */
        switch(strtoupper($method)) {
            case 'GET' :
                $opts[CURLOPT_URL] = $url . '?' . str_replace("&amp;", "&", http_build_query($params));
                break;
            case 'POST' :
                //判断是否传输文件
                //$params = $multi ? $params : http_build_query($params);
                $opts[CURLOPT_URL] = $url;
                $opts[CURLOPT_POST] = 1;
                $opts[CURLOPT_POSTFIELDS] = $params;
                break;
            default :
                throw new Exception('不支持的请求方式！');
        }

        /* 初始化并执行curl请求 */
        $ch = curl_init();
        curl_setopt_array($ch, $opts);
        $data = curl_exec($ch);
        $error = curl_error($ch);
        curl_close($ch);
        if ($error)
            throw new Exception('请求发生错误：' . $error);
        return $data;
    }

    function requestService($url, $data = null, $simaple = false,$is_json=false,$header = array())
    {

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, FALSE);
        if($header){
            curl_setopt($curl, CURLOPT_HTTPHEADER, $header);
        }
        if (!empty($data)&&$is_json==false){
            curl_setopt($curl, CURLOPT_POST, 1);
            curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
        }else{
            if($data){
                curl_setopt($curl, CURLOPT_POST, 1);
                curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data,JSON_UNESCAPED_UNICODE));
            }
        }

        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        $output = curl_exec($curl);
        $httpCode = curl_getinfo($curl, CURLINFO_HTTP_CODE);
        $error = curl_error($curl);
        //echo $curl;
        curl_close($curl);

        if($simaple) {
            $response = array('status' => 0, 'error' => '', 'code' => '0', 'msg' => '', $data = array());
            if($httpCode == 200) {
                $result = json_decode($output, true);
                if($result) {
                    $response['status'] = 1;
                    $response['code'] = $result['code'];
                    $response['msg'] = $result['msg'];
                    $response['data'] = $result['data'];
                } else {
                    $response['error'] = $output;
                }
            } else {
                $response['error'] = $error;
            }
            return $response;
        } else {
            return array(
                'httpCode' => $httpCode,
                'error' => $error,
                'output' => $output,
                'data' => json_decode($output, true),
            );
        }
    }
    // 通用结束

}